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

Question

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

Laurenz Albe · Accepted Answer · 2016-11-29 11:33:34Z

11

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;

answered Nov 29, 2016 at 11:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related