Postgresql Dynamic SQL Query Execution

Question

I have a function which generates a SELECT query dynamically using some If Else conditions .I have stored that query in a TEXT type variable as

    CREATE OR REPLACE FUNCTION func_updateanswercodes(ans_id_param BIGINT, 
                              que_id_param BIGINT, 
                              overwrite_param INTEGER, 
                              new_ans_code_param CHARACTER VARYING)
    RETURNS INTEGER AS
    $BODY$
...................
................
...................
        dyn_sql = 'SELECT ' || que_col_name || ' INTO old_ans_col_val FROM';
                    IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN
                        dyn_sql = dyn_sql || ' profile_answers JOIN registrations ON (pro_ans_frn_pro_id = reg_frn_pro_id)';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'reg_ans' THEN
                        dyn_sql = dyn_sql || ' reg_answers ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'tvl_ans' THEN
                        dyn_sql = dyn_sql || ' tvl_answers '; --35
                                END IF;
                    dyn_sql = dyn_sql || ' WHERE';
                    IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN
                        dyn_sql = dyn_sql || ' reg_id ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'reg_ans' THEN
                        dyn_sql = dyn_sql || ' reg_ans_frn_reg_id ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'tvl_ans' THEN
                        dyn_sql = dyn_sql || ' tvl_ans_frn_reg_id ';
                                END IF;
                        dyn_sql = dyn_sql || '= ' || CAST(temp_reg AS VARCHAR) ||';'

    /* Here want to execute that query in variable dync_sql
...........................
.............................
.......................
    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE

But with plpgsql(PL/SQL for postgres) I don't know how to execute this same query in a variable . Please help me with this . Thanks in Advance

Daniel · Accepted Answer · 2012-12-20 06:35:54Z

2

From the docs (Postgres 9.1):

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];
where command-string is an expression yielding a string (of type text) containing the command to be executed. The optional target is a record variable, a row variable, or a comma-separated list of simple variables and record/row fields, into which the results of the command will be stored.

In other words:

Move the INTO old_ans_col_val from the String to the place where you EXECUTE it.

answered Dec 20, 2012 at 6:35

Daniel

28.2k20 gold badges93 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Satish Sharma Over a year ago

Thanks @Daniel can u guide me same in Update query . I have a Update query in dyn_sql and want to set a column value , how can I use that dynamic Update query from dyn_sql varaible?

Eelke · Accepted Answer · 2012-12-20 06:34:58Z

1

Use EXECUTE for details see the manual

Note that you have to take your INTO part out of the query string and put it at the end.

EXECUTE dyn_sql INTO old_ans_col_val;

answered Dec 20, 2012 at 6:34

Eelke

22.3k5 gold badges57 silver badges77 bronze badges

Collectives™ on Stack Overflow

Postgresql Dynamic SQL Query Execution

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related