PostgreSQL - 错误：SQL 状态：XX000
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2446853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL - Error: SQL state: XX000
提问by rob
I have a table in Postgres that looks like this:
我在 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
);
And a select function that looks like this:
还有一个看起来像这样的选择函数：
CREATE OR REPLACE FUNCTION "Population_SelectAll"()
RETURNS SETOF "Population" AS
$BODY$select
"Id",
"Name",
"Description",
"IsVisible"
from "Population";
$BODY$
LANGUAGE 'sql' STABLE
COST 100
Calling the select function returns all the rows in the table as expected.
调用 select 函数会按预期返回表中的所有行。
I have a need to add a couple of columns to the table (both of which are foreign keys to other tables in the database). This gives me a new table def as follows:
我需要向表中添加几列（这两列都是数据库中其他表的外键）。这给了我一个新的表定义如下：
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
);
and function:
和功能：
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;
Calling the function after these changes results in the following error message:
在这些更改后调用该函数会导致以下错误消息：
ERROR: could not find attribute 11 in subquery targetlist
SQL state: XX000
What is causing this error and how do I fix it? I have tried to drop and recreate the columns and function - but the same error occurs.
是什么导致了这个错误，我该如何解决？我试图删除并重新创建列和函数 - 但发生了同样的错误。
Platform is PostgreSQL 8.4 running on Windows Server. Thanks.
平台是运行在 Windows Server 上的 PostgreSQL 8.4。谢谢。
回答by Scott Bailey
Did you dropping and recreating the function? By the way, you gotta love how user friendly Postgres is. What other database would you hugs and kisses(XXOOO) as an error state?
您是否删除并重新创建了该功能？顺便说一句，您一定会喜欢 Postgres 的用户友好性。你会拥抱和亲吻其他哪个数据库（XXOOO）作为错误状态？
回答by Mark
When I've seen something similar in the past, it was because the database connection cached certain function attributes. So if I was using pgAdmin, I had to close the SQL editor window and establish a new connection in order to get the function to work correctly. If you haven't already, be sure you are testing the function on new db connections.
我以前看到过类似的东西，那是因为数据库连接缓存了某些函数属性。因此，如果我使用 pgAdmin，我必须关闭 SQL 编辑器窗口并建立一个新连接才能使该功能正常工作。如果您还没有，请确保您正在新数据库连接上测试该功能。
I thought the issue was fixed a few versions ago in PostgreSQL, but it's worth a try.
我认为这个问题在 PostgreSQL 的几个版本前已经解决了，但值得一试。
回答by Sergey
Found a bit easier for me solution: created a backup of the database and restored it from this backup.
发现对我来说更容易解决方案：创建数据库的备份并从该备份中恢复它。
