Postgresql dynamic Update Query Execution

Question

I have a plpgsql block which generates a dynamic update query as :

 CREATE OR REPLACE FUNCTION fun_lenght_test () RETURNS VOID AS $BODY$
    BEGIN
    /*
    some code here
    */
    dyn_sql = 'UPDATE';
                            IF SUBSTR(que_col_name, 0, 8)   = 'pro_ans' THEN
                                dyn_sql = dyn_sql ||    ' profile_answers ';
                            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 ';
                            END IF;

                                dyn_sql = dyn_sql|| 'SET' || ' ' || que_col_name || ' = ' || NEW_VALUE_FOR_COLUMN ;

                                dyn_sql = dyn_sql || ' WHERE' ;

                            IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN
                                dyn_sql = dyn_sql || ' pro_ans_frn_pro_id = (SELECT pro_ans_frn_pro_id FROM profile_answers JOIN registrations ON (pro_ans_frn_pro_id = reg_frn_pro_id) WHERE reg_id ';
                            ELSIF SUBSTR(que_col_name, 0, 8) = 'reg_ans' THEN
                                dyn_sql = dyn_sql || ' reg_answers WHERE reg_ans_frn_reg_id ';
                            ELSIF SUBSTR(que_col_name, 0, 8) = 'tvl_ans' THEN
                                dyn_sql = dyn_sql || ' tvl_answers WHERE tvl_ans_frn_reg_id ';
                            END IF;

                            IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN  
                            dyn_sql = dyn_sql || '= ' || CAST(temp_reg AS VARCHAR) || ');';
                            ELSE
                            dyn_sql = dyn_sql || '= ' || CAST(temp_reg AS VARCHAR) || ';';

 /* want to execute this update dynamic query */
/*
some code here

*/
END;
$BODY$
LANGUAGE plpgsql

Can you please help me executing this dynamic Update query . Thanks In advance.

What is the postgresql version?

Clodoaldo Neto
– Clodoaldo Neto

2012-12-20 11:25:35 +00:00
Commented Dec 20, 2012 at 11:25 — Clodoaldo Neto
– Clodoaldo Neto, Commented Dec 20, 2012 at 11:25

Edward · Accepted Answer · 2012-12-20 07:59:53Z

1

CREATE OR REPLACE FUNCTION fun_lenght_test () RETURNS VOID AS $BODY$

DECLARE
dyn_sql varchar;
BEGIN
/*
some code here
*/
dyn_sql = 'UPDATE';
                 IF SUBSTR(que_col_name, 0, 8)   = 'pro_ans' THEN
                            dyn_sql = dyn_sql ||    ' profile_answers ';
                  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 ';
                  END IF;

                            dyn_sql = dyn_sql|| 'SET' || ' ' || que_col_name || ' = ' || NEW_VALUE_FOR_COLUMN ;

                            dyn_sql = dyn_sql || ' WHERE' ;

                        IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN
                            dyn_sql = dyn_sql || ' pro_ans_frn_pro_id = (SELECT pro_ans_frn_pro_id FROM profile_answers JOIN registrations ON (pro_ans_frn_pro_id = reg_frn_pro_id) WHERE reg_id ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'reg_ans' THEN
                            dyn_sql = dyn_sql || ' reg_answers WHERE reg_ans_frn_reg_id ';
                        ELSIF SUBSTR(que_col_name, 0, 8) = 'tvl_ans' THEN
                            dyn_sql = dyn_sql || ' tvl_answers WHERE tvl_ans_frn_reg_id ';
                        END IF;

                        IF SUBSTR(que_col_name, 0, 8) = 'pro_ans' THEN  
                        dyn_sql = dyn_sql || '= ' || CAST(temp_reg AS VARCHAR) || ');';
                        ELSE
                        dyn_sql = dyn_sql || '= ' || CAST(temp_reg AS VARCHAR) || ';';
            END IF;

END; $BODY$ LANGUAGE plpgsql

I am sorry to know what do you want, but this can run...

answered Dec 20, 2012 at 7:59

Edward

2591 gold badge4 silver badges16 bronze badges

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

3 Comments

Satish Sharma Over a year ago

At Last This dyn_sql contains a Update Query as per the If and Else condition , I want to execute this update query.

Edward Over a year ago

It seems that you forget to declare the type of dyn_sql and 'END IF;' statement at the end of the last if ...

Satish Sharma Over a year ago

Srry for my posting it is working and thanks a lot for directing me to my mistake.

Collectives™ on Stack Overflow

Postgresql dynamic Update Query Execution

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related