postgresql 在不能接受集合的上下文中调用的集合值函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41112493/
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
set-valued function called in context that cannot accept a set
提问by Nulik
I am receiving the error:
我收到错误：
set-valued function called in context that cannot accept a set
在不能接受集合的上下文中调用的集合值函数
when executing this function at RETURN QUERY EXECUTEline:
RETURN QUERY EXECUTE在行执行此函数时：
PLSQL $ cat lookup_email.pl
CREATE OR REPLACE FUNCTION app.lookup_email(ident_id bigint,sess bigint,company_id bigint,email varchar)
RETURNS SETOF RECORD as $$
DECLARE
rec RECORD;
comp_id bigint;
server_session bigint;
schema_name varchar;
query varchar;
BEGIN
schema_name:='comp' || company_id;
select app.session.session into server_session from app.session where app.session.identity_id=ident_id and app.session.session=sess;
IF FOUND
THEN
BEGIN
query:='SELECT i.email,u.user_id FROM app.identity as i,' || schema_name || '.uzer as u WHERE i.email like ''%' || email || '%'' and i.identity_id=u.identity_id';
RAISE NOTICE 'executing: %',query;
RETURN QUERY EXECUTE query;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE ' query error (%)',SQLERRM;
END;
END IF;
END;
$$ LANGUAGE plpgsql;
This is the ouput from psql:
这是 psql 的输出：
dev=> select app.lookup_email(4,730035455897450,6,'u');
NOTICE: executing: SELECT i.email,u.user_id FROM app.identity as i,comp6.uzer as u WHERE i.email like '%u%' and i.identity_id=u.identity_id
NOTICE: query error (set-valued function called in context that cannot accept a set)
lookup_email
--------------
(0 rows)
I know the query doesn't contain any error, because it works in another psql session:
我知道查询不包含任何错误，因为它在另一个 psql 会话中工作：
dev=> SELECT i.email,u.user_id FROM app.identity as i,comp6.uzer as u WHERE i.email like '%u%' and i.identity_id=u.identity_id;
email | user_id
----------------+---------
[email protected] | 1
(1 row)
So why is Postgres complaining if I declared my function being as RETURNS SETOF RECORD? Where is my error?
那么为什么 Postgres 会抱怨如果我将我的函数声明为 asRETURNS SETOF RECORD呢？我的错误在哪里？
采纳答案by Evan Carroll
So, why is Postgres complaining if I declared my function being a SET of RECORD ??? Where is my error?
那么，如果我声明我的函数是一组 RECORD，为什么 Postgres 会抱怨？？？我的错误在哪里？
- Call your Set Returning Function in a FROM clause.
- Always specify your types.
- 在 FROM 子句中调用您的 Set 返回函数。
- 始终指定您的类型。
It's called a Set Returning Function, but you want to specify the composite type
它被称为Set Returning Function，但您想指定复合类型
This is totally valid,
这是完全有效的，
RETURNS SETOF RECORD $$
However, you may have to call it with,
但是，您可能必须调用它，
SELECT email, user_id
FROM
app.lookup_email(4,730035455897450,6,'u')
AS t(email text, user_id integer)
The context which you can not call an untyped SRF in, is one which does not have a table-definition. This syntax can get nasty, so just it's easier to change RETURNS SETOF RECORDto
您不能在其中调用无类型 SRF 的上下文是没有表定义的上下文。此语法可能会变得令人讨厌，因此更容易更改RETURNS SETOF RECORD为
RETURNS TABLE(email text, user_id integer) AS $$
and use the function without the column definition list
并使用没有列定义列表的函数
SELECT email, user_id
FROM app.lookup_email(4,730035455897450,6,'u')
Find more information in the docs
在文档中查找更多信息
回答by Lester
defind output column name in function like down below
在如下所示的函数中定义输出列名称
CREATE OR REPLACE FUNCTION app.lookup_email(ident_id bigint,sess bigint,company_id bigint,email varchar, OUT <column_name> <data type>, OUT ...)
