andrew
andrew

Reputation: 135

postgresql, pgadmin error RETURN cannot have a parameter in function returning set

I made a function like this

create or replace function get_source2(TableName character varying)
returns setof integer as
$$
declare
    geom geometry;
    snode integer;
begin
    execute ('select get_source(geom) from '|| TableName)
        into snode;
    return snode;
end
$$
language plpgsql;

output data would be array type

so I try to use setof.

If I don't use setof, it runs well but only one result comes out.

However if I use setof I get an error like that

error : RETURN cannot have a parameter in function returning set

Upvotes: 9

Views: 17089

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248215

Be careful! The way your function is written it is vulnerable to SQL injection!

You should use

$$BEGIN
   RETURN QUERY EXECUTE 'SELECT get_source(geom) FROM '
                        || quote_ident(TableName);
END;$$

Then RETURNS SETOF integer will work well.

Read up in the documentation how to use RETURN in a table function:

Either you do

RETURN NEXT expression1;
RETURN NEXT expression2;
...
RETURN;

or

RETURN QUERY SELECT ...;

or

RETURN QUERY EXECUTE 'SELECT ...';

but never

RETURN expression;

Upvotes: 11

Related Questions