CODEWITHSUNDEEP
sqlpostgresqlplpgsqldynamic-sql
Matthew Gunn
Matthew Gunn

Reputation: 4519

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

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.)

Upvotes: 1

Views: 1059

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657867

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:

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

Upvotes: 2

JosMac
JosMac

Reputation: 2312

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.

Upvotes: 1

Related Questions