sql - PostgreSQL - 错误 : SQL state: XX000

Question

我在 Postgres 中有一个如下所示的表:

CREATE TABLE "Population"
(
  "Id" bigint NOT NULL DEFAULT nextval('"population_Id_seq"'::regclass),
  "Name" character varying(255) NOT NULL,
  "Description" character varying(1024),
  "IsVisible" boolean NOT NULL
  CONSTRAINT "pk_Population" PRIMARY KEY ("Id")
)
WITH (
  OIDS=FALSE
);

还有一个看起来像这样的选择函数:

CREATE OR REPLACE FUNCTION "Population_SelectAll"()
  RETURNS SETOF "Population" AS
$BODY$select
  "Id",
  "Name",
  "Description",
  "IsVisible"
from "Population";
$BODY$
  LANGUAGE 'sql' STABLE
  COST 100

调用 select 函数会按预期返回表中的所有行。

我需要向表中添加几列(它们都是数据库中其他表的外键)。这给了我一个新的表定义，如下所示:

CREATE TABLE "Population"
(
  "Id" bigint NOT NULL DEFAULT nextval('"population_Id_seq"'::regclass),
  "Name" character varying(255) NOT NULL,
  "Description" character varying(1024),
  "IsVisible" boolean NOT NULL,
  "DefaultSpeciesId" bigint NOT NULL,
  "DefaultEcotypeId" bigint NOT NULL,
  CONSTRAINT "pk_Population" PRIMARY KEY ("Id"),
  CONSTRAINT "fk_Population_DefaultEcotypeId" FOREIGN KEY ("DefaultEcotypeId")
      REFERENCES "Ecotype" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "fk_Population_DefaultSpeciesId" FOREIGN KEY ("DefaultSpeciesId")
      REFERENCES "Species" ("Id") MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);

和函数:

CREATE OR REPLACE FUNCTION "Population_SelectAll"()
  RETURNS SETOF "Population" AS
$BODY$select
  "Id",
  "Name",
  "Description",
  "IsVisible",
  "DefaultSpeciesId",
  "DefaultEcotypeId"
from "Population";
$BODY$
  LANGUAGE 'sql' STABLE
  COST 100
  ROWS 1000;

在这些更改后调用该函数会导致以下错误消息:

ERROR: could not find attribute 11 in subquery targetlist
SQL state: XX000

是什么导致了这个错误，我该如何解决？我尝试删除并重新创建列和函数 - 但出现了同样的错误。

平台是在 Windows Server 上运行的 PostgreSQL 8.4。谢谢。

Answer 1

您是否删除并重新创建了函数？顺便说一句，您一定会喜欢 Postgres 的用户友好性。您还会拥抱和亲吻 (XXOOO) 哪个其他数据库作为错误状态？