Postgres: invalid type name “query%ROWTYPE”

问题:

I want to create a variable based on the table "query" (l_query query%ROWTYPE), but I got this message: invalid type name "query%ROWTYPE" I also tried to use the fully qualified table name l_query dbname.public.query%ROWTYPE, but it didn't help me ether.

CREATE OR REPLACE FUNCTION somefunc() RETURNS int AS $$ DECLARE l_res dbname.public.query%ROWTYPE; BEGIN return 1; END; $$ LANGUAGE plpgsql;

PS:I do have the table query. I checked it a couple of times. I have this error only on the production server. Locally, I ran it with no problems

version

PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

回答1:

A non-quoted reference to query probably confuses the plpgsql parser since QUERY is also a keyword used in the RETURN QUERY ... construct.

The generic solution is to add double quotes around the problematic identifier:

DECLARE l_res "query"%ROWTYPE;

As for your fully qualified variant, there's another problem with it: only a schema name may prefix a table name, not a database name plus schema name.

These declarations should also work:

DECLARE l_res public."query"%ROWTYPE;

or

DECLARE l_res "public"."query"%ROWTYPE;