Table as an argument of a PostgreSQL function

Question

I'm trying to pass a table as an argument in a PostgreSQL function, and I can't find the right keyword (what should be instead of table in my code) in the first line :

CREATE FUNCTION test.fcttest(table)   
RETURNS SETOF test.out_table
AS $$
    INSERT INTO test.out_table /* obviously, the schema for this table is what it should be, an int column for A.id and a varchar(30) column for A.another_field */
        SELECT A.id, A.another_field
        FROM $1 A;
    SELECT A.id, A.another_field 
        FROM $1 A;
$$ 
LANGUAGE SQL;

Or maybe it just can't work like this ? If so, how should I do it ?

(Also, I guess there might also be SQL injection related issues with my function, but please ignore them for now, it's for a prototype in a secure environment so I don't have to worry about it for the moment. Or if you do address it, please do so in a separate part of your answer so I can understand what adresses what :) ).

Thanks.

Patrick · Accepted Answer · 2016-03-18 17:02:25Z

1

You can not pass a table as such as a parameter, only the name of the table:

CREATE FUNCTION test.fcttest(tbl text) RETURNS SETOF test.out_table AS $$
BEGIN
  EXECUTE format('INSERT INTO test.out_table '
                   'SELECT id, another_field FROM %I', tbl);
  RETURN QUERY EXECUTE format('SELECT id, another_field FROM %I', tbl);
END;
$$ LANGUAGE plpgsql;

When passing a table name you need to execute a dynamic command. You can only do this in a plpgsql function. The use of the format() function with the %I modifier protects against SQL injection. The rows are returned using the RETURN NEXT QUERY phrase, again with a dynamic command.

Note that this logic both inserts a set of records into table test.out_table and then returns the same set of records. Not sure if that is what you really want.

edited Mar 18, 2016 at 17:02

answered Mar 18, 2016 at 16:57

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

4 Comments

François M. Over a year ago

Alright, I think I understand how it works, thank you, I'm trying it out asap. Just out of curiosity, if I wanted to do just the insertion and return nothing, I'd have to change line 1 as ... RETURNS void AS $$ and line 5-6 as RETURN END;, right ?

François M. Over a year ago

And another followup : what would the syntax be for the EXECUTE ... line (especially the ... FROM %I', tbl ... part) if I was doing a join between two tables that were in the arguments ?

Patrick Over a year ago

That's very simple. Say you have tbl1 and tbl2 as arguments then you simply put %I wherever you want the table name (an identifier, hence %I). Something like format('SELECT * FROM %1$I JOIN %2$I ON %1$I.field = %2$I.col', tbl1, tbl2) The format replaces every placeholder (%I, %L or %s) with the next argument of the function (tbl1, tbl2). You can "re-use" previously used arguments with the %1$I syntax. See the format() documentation (link in answer).

Neil McGuigan Over a year ago

i would use name as the parameter type

Collectives™ on Stack Overflow

Table as an argument of a PostgreSQL function

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related