How to use declared parameters as component of a query in Pl/pgSQL ?

Question

Guys,

I am tryingto do a generic function in pl/pgsql. I am facing a problem that I can't figure out. See in the declare block I assigned value to some parameters. I would like to know how to use them as component of a query. (example : the BEGIN/END block, groupby_join_field)

  CREATE OR REPLACE FUNCTION drillThrough(whereclause anyarray, groupbyclause anyarray) RETURNS void AS $$

    DECLARE
            where_table varchar(19) := whereclause[1];
            where_join_field varchar(19) := whereclause[2];
            where_value_field varchar(19) := whereclause[3];
            where_value varchar(19) := whereclause[4];

            groupby_table varchar(18) := groupbyclause[1];
            groupby_join_field varchar(18) := groupbyclause[2];
            groupby_value_field varchar(18) := groupbyclause[3];
    BEGIN
            INSERT INTO test SELECT dim_date.groupby_join_field, 1 FROM dim_date;
    END;
    $$ LANGUAGE plpgsql;

Every idea is welcome,

Edit :

In this case goupby_join_field has the value of year_id, so I would like the engine to understand dim_date.year_id

Answer 1

I don't know if I understood your question correctly, but I'll give it a shot.

You can execute arbitrary strings with the pl/pgSQL EXECUTE command. So you just have to build your query as a string.