set-valued function called in context that cannot accept a set
So, why is Postgres complaining if I declared my function being a SET of RECORD ??? Where is my error?
- Call your Set Returning Function in a FROM clause.
- 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
Nulik
A C developer, obsessed with performance. Contact for development on blockchain platforms: [email protected]
Updated on July 10, 2022Comments
-
Nulik almost 2 years
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?-
Erwin Brandstetter over 7 yearsWhat Evan explains is all true and good, but you still shouldn't get this error with current Postgres. What's your version? (
SELECT version()
)
-
-
Luan Huynh over 7 yearsI like to use
returns table (col_1 type, col_2 type)
thancasting
returned data `select x,y from function_a() as (x type, y type).