PostgreSQL - Error: SQL state: XX000.

Question

I have a table in Postgres that looks like this:

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.

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.

Answer 1

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.

I thought the issue was fixed a few versions ago in PostgreSQL, but it's worth a try.

Answer 2

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?