Execute String Postgres

Question

I am having trouble getting this code to work. This is executed as a result of a insert, update or delete trigger. The same code is going to be called from several table triggers. Therefore, I don't know pre-hand what the table names are or what their columns are. Any ideas?

EXECUTE format('INSERT INTO %s VALUES %s', _tbl, (OLD).*);

Error is about the strings not being surrounded with quotes:

ERROR:  column "bangor" does not exist
LINE 1: INSERT INTO sample_test_table_deletes VALUES (3,bangor,D,"20...
                                                          ^

On the other hand, the following works:

INSERT INTO my_table VALUES((OLD).*);

That is, when the query is not through an execute block.

Update (Trigger Code):

CREATE OR REPLACE FUNCTION sample_trigger_func() RETURNS TRIGGER AS $$
DECLARE
    operation_code char;
    table_name varchar(50);
    delete_table_name varchar(50);
    old_id integer; 

BEGIN
    table_name = TG_TABLE_NAME;
    delete_table_name = TG_TABLE_NAME || '_deletes';

    SELECT SUBSTR(TG_OP, 1, 1)::CHAR INTO operation_code;

    IF TG_OP = 'DELETE' THEN
        OLD.mod_op = operation_code;
        OLD.mod_date = now();

        RAISE INFO 'OLD: %', (OLD).name;

        EXECUTE format('INSERT INTO %s VALUES %s', delete_table_name, (OLD).*);

    ELSE
        EXECUTE format('UPDATE TABLE %s SET mod_op = %s AND mod_date = %s'
                      , TG_TABLE_NAME, operation_code, now());
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

What is the error you get? Where does `_tbl` com from? What is the **complete** code of the trigger? — , May 04 '15 at 13:54
And what about the trigger code? Please don't play 20 questions with us... — Kevin, May 04 '15 at 14:10
You write: `This is executed as a result of a delete trigger.` But then you have an `ELSE` twig for `INSERT` / `UPDATE` .. which is completely invalid. Clarify what you are trying to do, please. I doubt every possible *original* table has a column `mod_op`? I suggest you start a new question. Focus this one on the topic asked and put the rest in a new question. — Erwin Brandstetter, May 04 '15 at 14:47
Erwin, actually, it is not a delete trigger, but an insert, update or delete trigger. I am sorry for the confusion. Also, yes, every table has a mod_op column. I updated the question. Do you have anything else wrong in this too? — picmate 涅, May 04 '15 at 15:25

score 3 · Accepted Answer · edited May 23 '17 at 11:51

3

The special variable OLD is not visible inside EXECUTE. (Nor is any other variable in the scope of the function.) Pass in values with the USING construct:

EXECUTE format('INSERT INTO %I VALUES (($1).*)', _tbl)
USING OLD;

Also, use %I for the identifier, not %s.

Details:

INSERT with dynamic table name in trigger function

edited May 23 '17 at 11:51

Community

1
1

answered May 04 '15 at 14:19

Erwin Brandstetter

605,456
145
1,078
1,228

But your added trigger function has a couple of additional serious problems. – Erwin Brandstetter May 04 '15 at 14:44
Hi Erwin. Could you suggest the improvements? Do they need to come in the form of answers to a different question? Thanks. – picmate 涅 May 04 '15 at 15:20
1

@picmate: I answered your question asked and then saw the problems in the function you added later. The policy here is to ask one question per issue to keep things clear and useful for the general public. – Erwin Brandstetter May 04 '15 at 15:26
Hi Erwin. I started a new question: http://stackoverflow.com/questions/30034194/problems-with-the-trigger. Hope you will be able to take a look at it and give some suggestions. – picmate 涅 May 04 '15 at 15:43

Execute String Postgres

1 Answers1