Trying to create dynamic query strings with PL/PgSQL to make DRY functions in PostgreSQL 9.6

Question

I have tables that contain the same type of data for every year, but the data gathered varies slightly in that they may not have the same fields.

d_abc_2016
d_def_2016
d_ghi_2016
d_jkl_2016

There are certain constants for each table: company_id, employee_id, salary.

However, each one might or might not have these fields that are used to calculate total incentives: bonus, commission, cash_incentives. There are a lot more, but just using these as a examples. All numeric

I should note at this point, users only have the ability to run SELECT statements.

What I would like to be able to do is this:

1. Give the user the ability to call in SELECT and specify their own fields in addition to the call
2. Pass the table name being used into the function to use in conditional logic to determine how the query string should be constructed for the eventual total_incentives calculation in addition to passing the whole table so a ton of arguments don't have to be passed into the function

Basically this:

SELECT employee_id, salary, total_incentives(t, 'd_abc_2016')
FROM d_abc_2016 t;

So the function being called will calculate total_incentives which is numeric for that employee_id and also show their salary. But the user might choose to add other fields to look at.

For the function, because the fields used in the total_incentives function will vary from table to table, I need to create logic to construct the query string dynamically.

CREATE OR REPLACE FUNCTION total_incentives(ANYELEMENT, t text)
    RETURNS numeric AS
$$
DECLARE
    -- table name lower case in case user typed wrong
    tbl          varchar(255) := lower($2;

    -- parse out the table code to use in conditional logic
    tbl_code     varchar(255) := split_part(survey, '_', 2);

    -- the starting point if the query string
    base_calc    varchar(255) := 'salary + '

    -- query string
    query_string varchar(255);

    -- have to declare this to put computation INTO
    total_incentives_calc numeric;
BEGIN
    IF tbl_code = 'abc' THEN
        query_string := base_calc || 'bonus';
    ELSIF tbl_code = 'def' THEN
        query_string := base_calc || 'bonus + commission';
    ELSIF tbl_code = 'ghi' THEN
        -- etc...
    END IF;

    EXECUTE format('SELECT $1 FROM %I', tbl)
    INTO total_incentives_calc
    USING query_string;

    RETURN total_incentives_calc;
END;
$$
LANGUAGE plpgsql;

This results in an:

ERROR:  invalid input syntax for type numeric: "salary + bonus"
CONTEXT:  PL/pgSQL function total_incentives(anyelement,text) line 16 at EXECUTE

Since it should be returning a set of numeric values. Change it to the following:

CREATE OR REPLACE FUNCTION total_incentives(ANYELEMENT, t text)
    RETURNS SETOF numeric AS
$$
...
    RETURN;

Get the same error.

Figure well, maybe it is a table it is trying to return.

CREATE OR REPLACE FUNCTION total_incentives(ANYELEMENT, t text)
    RETURNS TABLE(tot_inc numeric) AS
$$
...

Get the same error.

Really, any variation produces that result. So really not sure how to get this to work.

Look at RESULT QUERY, RESULT NEXT, or RESULT QUERY EXECUTE.

https://www.postgresql.org/docs/9.6/static/plpgsql-control-structures.html

RESULT QUERY won't work because it takes a hard coded query from what I can tell, which won't take in variables.

RESULT NEXT iterates through each record, which I don't think will be suitable for my needs and seems like it will be really slow... and it takes a hard coded query from what I can tell.

RESULT QUERY EXECUTE sounds promising.

-- EXECUTE format('SELECT $1 FROM %I', tbl)
-- INTO total_incentives_calc
-- USING query_string;

RETURN QUERY 
    EXECUTE format('SELECT $1 FROM %I', tbl)
    USING query_string;

And get:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type character varying does not match expected type numeric in column 1.
CONTEXT:  PL/pgSQL function total_incentives(anyelement,text) line 20 at RETURN QUERY

It should be returning numeric.

Lastly, I can get this to work, but it won't be DRY. I'd rather not make a bunch of separate functions for each table with duplicative code. Most of the working examples I have seen have the whole query in the function and are called like such:

SELECT total_incentives(d_abc_2016, 'd_abc_2016');

So any additional columns would have to be specified in the function as:

EXECUTE format('SELECT employee_id...)

Given the users will only be able to run SELECT in query this really isn't an option. They need to specify any additional columns they want to see inside a query.

I've posted a similar question but was told it was unclear, so hopefully this lengthier version will more clearly explain what I am trying to do.

Answer 1

The column names and tables names should not be used as query parameters passed by USING clause.

Probably lines:

RETURN QUERY 
    EXECUTE format('SELECT $1 FROM %I', tbl)
    USING query_string;

should be:

RETURN QUERY 
    EXECUTE format('SELECT %s FROM %I', query_string, tbl);

This case is example why too DRY principle is sometimes problematic. If you write it directly, then your code will be simpler, cleaner and probably shorter.

Dynamic SQL is one from last solution - not first. Use dynamic SQL only when your code will be significantly shorter with dynamic sql than without dynamic SQL.