postgresql 查询的结构与函数结果类型、RETURNS TABLE 不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14685823/
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
Structure of query does not match function result type, RETURNS TABLE
提问by Tomas Greif
I need a simple function to return dynamic set of columns. I've found couple of examples on SO and end up with the following:
我需要一个简单的函数来返回动态列集。我在 SO 上找到了几个例子，结果如下：
CREATE or replace FUNCTION getColumn(_column1 text, _column2 text, _column3 text, _table text)
RETURNS TABLE(cmf1 text, cmf2 text, cmf3 text) AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT '
|| quote_ident(_column1)::text || ' as cmf1,'
|| quote_ident(_column2)::text || ' as cmf2,'
|| quote_ident(_column3)::text || ' as cmf3'
' FROM '
|| quote_ident(_table);
END;
$$ LANGUAGE plpgsql;
I need this function to work only with varchar/text columns so I created this testing table:
我需要这个函数只能用于 varchar/text 列，所以我创建了这个测试表：
create table test20130205 (
a text,
b text,
c varchar,
d text)
;
Finally, I can run some tests:
最后，我可以运行一些测试：
select * from getColumn('a','b','d','test20130205');
-- ok
select * from getColumn('a','b','c','test20130205');
-- error
ERROR: structure of query does not match function result type
DETAIL: Returned type character varying does not match expected type text in column 3.
CONTEXT: PL/pgSQL function getcolumn(text,text,text,text) line 3 at RETURN QUERY
It seems like type for column c (varchar) is checked before cast - this seems strange, but I guess I've missed something.
似乎在转换之前检查了列 c (varchar) 的类型 - 这看起来很奇怪，但我想我错过了一些东西。
How can I fix my function?
如何修复我的功能？
(PostgreSQL 9.1)
(PostgreSQL 9.1)
回答by Daniel Vérité
In your current function, the casts to text do not apply to the output columns values, they apply to their names (the result of quote_ident).
在您当前的函数中，对文本的转换不适用于输出列值，它们适用于它们的名称（ 的结果quote_ident）。
The cast should be moved inside the query itself:
演员表应该在查询本身内移动：
CREATE or replace FUNCTION getColumn(_column1 text, _column2 text, _column3 text, _table text)
RETURNS TABLE(cmf1 text, cmf2 text, cmf3 text) AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT '
|| quote_ident(_column1) || '::text as cmf1,'
|| quote_ident(_column2) || '::text as cmf2,'
|| quote_ident(_column3) || '::text as cmf3'
' FROM '
|| quote_ident(_table);
END;
$$ LANGUAGE plpgsql;
