Postgres:无效的类型名称"query％ROWTYPE" [英] Postgres: invalid type name "query%ROWTYPE"
问题描述
我想基于表"query"(l_query query%ROWTYPE)创建一个变量，但是我得到了以下消息:invalid type name "query%ROWTYPE"
我还尝试使用完全限定的表名l_query dbname.public.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;
CREATE OR REPLACE FUNCTION somefunc()
RETURNS int AS $$
DECLARE
l_res dbname.public.query%ROWTYPE;
BEGIN
return 1;
END;
$$ LANGUAGE plpgsql;
PS:我确实有表格查询.我检查了几次.我仅在生产服务器上有此错误.在本地，我没有问题地运行了
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
x86_64-unknown-linux-gnu上的PostgreSQL 9.2.4，由gcc(Debian 4.7.2-5)4.7.2，64位编译
PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
推荐答案
对query的无引号引用可能会使plpgsql解析器感到困惑，因为QUERY也是RETURN QUERY ...构造中使用的关键字.
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;
或
DECLARE l_res "public"."query"%ROWTYPE;
这篇关于Postgres:无效的类型名称"query％ROWTYPE"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
