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

Question

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 
----------------+---------
 [email protected] |       1
(1 row)

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

Luik

What Evan explains is all true and good, but you still shouldn't get this error with current Postgres. What's your version? (SELECT version()) – Sclerotic

Answer 1

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

Notification

I like to use returns table (col_1 type, col_2 type) than casting returned data `select x,y from function_a() as (x type, y type). – Maurili

Answer 2

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 ...)

Terrilyn