Postgresql function with table as input? Is dynamic SQL in a plpgsql function the way to go?

Question

I have a common situation that comes up where I want to join with a table moredata. To give the simplest example, imagine I have:

SELECT *
FROM x 
JOIN moredata d on x.yyyymmdd = d.yyyymmdd

x will vary while moredata stays the same. What's a good way stylistically to reuse code, automate the above so that I can apply it to different tables (eg. do the same thing on table 'y' instead of 'x')?

One way would be to write a plpgsql function using dynamic SQL that operates on the name of the table. Something like:

CREATE FUNCTION joinmoredata(tblname text) RETURNS TABLE(...) AS $$
BEGIN
  RETURN QUERY EXECUTE format('SELECT * FROM %I JOIN moredata on ...', tblname);
END;
$$ LANGUAGE plpgsql;

But then you couldn't apply it to a derived table or a CTE. Is there a better way? Or is dynamic SQL the best way to reuse code in this situation?

(Note, the actual query is a lot more complicated than SELECT * FROM x JOIN moredata d on x.yyyymmdd = d.yyyymmdd which is why I want to reuse code.)

`x will vary` .. how exactly? Does each table have the same columns? (types, names, number)? And will the return type `RETURNS TABLE (...)` vary also? — Erwin Brandstetter, Dec 10 '16 at 04:35
@ErwinBrandstetter `x` can be standardized so that it has the same columns each time and the table the functions returns has the same columns each time. — Matthew Gunn, Dec 10 '16 at 05:34

score 2 · Accepted Answer · edited May 23 '17 at 12:33

As long as you use the same column names with the same data types from each table in place of x and return the same row type, that's a good candidate for dynamic SQL. Exactly like you already have in your question. Your use of format() with %I suggests you are aware of how to sanitize identifiers avoid syntax errors and SQL injection.

You need to have a plan for privilege management. And I would pass schema-qualified table names or data type regclass to avoid confusion with table name resolution and possibly other tables with the same name in a different schema.

Closely related, with more details:

Table name as a PostgreSQL function parameter

If it gets more complicated - varying column names and types - you can always concatenate query strings in a client program ...

score 1 · Answer 2 · answered Dec 09 '16 at 09:08

I can only say that I use dynamic SQL any time I need to reuse bigger parts of SQL code and change only something in WHERE clause or table names etc. And it works OK and I do this way also very complicated CTE queries containing also INSERTs or UPDATESs and it works. So I would vote for dynamic SQL.

Postgresql function with table as input? Is dynamic SQL in a plpgsql function the way to go?

2 Answers2