Dynamic FROM clause in Postgres

Question

Using PostgreSQL 9.1.13 I've written the followed query to calculate some data:

WITH windowed AS (
    SELECT a.person_id, a.category_id,
        CAST(dense_rank() OVER w AS float) / COUNT(*) OVER (ORDER BY category_id) * 100.0 AS percentile
    FROM (
        SELECT DISTINCT ON (person_id, category_id) *
        FROM performances s
        -- Want to insert a FROM clause here
        INNER JOIN person p ON s.person_id = p.ident
        ORDER BY person_id, category_id, created DESC
    ) a
    WINDOW w AS (PARTITION BY category_id ORDER BY score)
)
SELECT category_id,percentile FROM windowed 
WHERE person_id = 1;

I now want to turn this into a stored procedure but my issue is that in the middle there, where I showed the comment, I need to place a dynamic WHERE clause. For example, I'd like to add something like:

WHERE p.weight > 110 OR p.weight IS NULL

The calling application let's people pick filters and so I want to be able to pass the appropriate filters into the query. There could be 0 or many filters, depending on the caller, but I could pass it all in as a properly formatted where clause as a string parameter, for example.

The calling application just sends values to a webservice, which then builds the string and calls the stored procedure, so SQL injection attacks won't really be an issue.

You can use [`EXECUTE IMMEDIATE`](http://www.postgresql.org/docs/current/static/ecpg-sql-execute-immediate.html) to execute any string as an SQL query. — Ihor Romanchenko, Aug 10 '14 at 21:08
You can use parameters instead of passing in an SQL string, if you know all possible filters in advance. See here for some examples: http://www.postgresql.org/docs/9.2/static/xfunc-sql.html — jmiserez, Aug 10 '14 at 21:12
What you display is not a `FROM` clause but a set of `WHERE` conditions and cannot go where you indicated. For an answer in code one would need *exact* specifications of the possible `WHERE` conditions. — Erwin Brandstetter, Aug 10 '14 at 23:49

score 0 · Accepted Answer · edited May 23 '17 at 12:06

The calling application just sends values to a webservice, which then builds the string and calls the stored procedure, so SQL injection attacks won't really be an issue.

Too many cooks spoil the broth.

Either let your webserive build the SQL statement or let Postgres do it. Don't use both on the same query. That leaves two possible weak spots for SQL injection attacks and makes debugging and maintenance a lot harder.

Here is full code example for a plpgsql function that builds and executes an SQL statement dynamically while making SQL injection impossible (just from two days ago):

Robust approach for building SQL queries programmatically

Details heavily depend on exact requirements.

Dynamic FROM clause in Postgres

1 Answers1