在不能接受集合的上下文中调用的集合值函数 [英] set-valued function called in context that cannot accept a set

问题描述

我收到错误:

在无法接受集合的上下文中调用的集合值函数

在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;

这是来自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)

我知道查询不包含任何错误，因为它可以在另一个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 
----------------+---------
 [e-mail] |       1
(1 row)

那么，如果我声明函数为RETURNS SETOF RECORD，为什么Postgres会抱怨呢?我的错误在哪里?

解决方案

那么，如果我声明我的函数是一个RECORD SET，为什么Postgres会抱怨呢?我的错误在哪里?

1. 在FROM子句中调用集返回函数.
2. 始终指定您的类型.

它被称为 Set Returning Function ，但是您要指定复合类型

这是完全有效的

RETURNS SETOF RECORD $$

但是，您可能必须用它来调用

SELECT email, user_id
FROM 
    app.lookup_email(4,730035455897450,6,'u')
    AS t(email text, user_id integer)

不能在其中调用无类型SRF的上下文是没有表定义的上下文.这种语法可能会令人讨厌，因此将RETURNS SETOF RECORD更改为

更容易

RETURNS TABLE(email text, user_id integer) AS $$

并使用没有列定义列表的功能

SELECT email, user_id
FROM app.lookup_email(4,730035455897450,6,'u')

在文档

I am receiving the error:

set-valued function called in context that cannot accept a set

when executing this function at RETURN QUERY EXECUTE line:

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:

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:

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 
----------------+---------
 [e-mail] |       1
(1 row)

So why is Postgres complaining if I declared my function being as RETURNS SETOF RECORD? Where is my error?

解决方案

So, why is Postgres complaining if I declared my function being a SET of RECORD ??? Where is my error?

1. Call your Set Returning Function in a FROM clause.
2. Always specify your types.

It's called a Set Returning Function, but you want to specify the composite type

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 RECORD to

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

这篇关于在不能接受集合的上下文中调用的集合值函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！