Postgres: How to get the value of a dynamic column from triggers

Question

I am trying to get the value of a column so that it can be inserted into a table that holds the column name and column value of the inserted row, however, I have not been able to get the value of the column that I need. Normally, I would be able to use value := NEW.column_name but each table has a unique key column name that is in the table name itself (I know, that's bad), but I already have a way to get the column name that I want, it's getting the NEW value of that column that's the problem.

    CREATE OR REPLACE FUNCTION trgfn_keyvalue_insert()
    RETURNS trigger AS
    $BODY$
    DECLARE
       key_column_value character varying;
       key_column_name character varying;
       part text;
       part_array text[] := array['prefix_','_suffix'];
    BEGIN    
       key_column_name := TG_TABLE_NAME;     --parsing the table name to get the desired column name
       FOREACH part IN ARRAY part_array LOOP
          key_column_name = regexp_replace(cat, part, '');
       END loop; 

       IF TG_OP = 'INSERT' THEN    
       EXECUTE 'SELECT $1 FROM $2'           --This is where I'd like to get the 
          INTO key_column_value              --value of the column
          USING key_column_name, NEW;    

       INSERT INTO inserted_kvp
          (table_name, key, value)
          VALUES 
          (TG_TABLE_NAME, key_column_name, key_column_value);
       END IF;

       RETURN NEW;
    END;
    $BODY$
       LANGUAGE plpgsql VOLATILE

So, when I INSERT into a table:

    CREATE TABLE prefix_kvp1_suffix AS id SERIAL, kvp1 CHARACTER VARYING;
    CREATE TABLE prefix_kvp2_suffix AS id SERIAL, kvp2 CHARACTER VARYING;

    INSERT INTO prefix_kvp1_suffix VALUES (1, 'value1');
    INSERT INTO prefix_kvp2_suffix VALUES (1, 'value2');

I would like for the inserted_kvp table to have the following:

    |    table_name    |key |value |
    --------------------------------
    |prefix_kvp1_suffix|kvp1|value1|
    |prefix_kvp2_suffix|kvp2|value2|

Instead, I get the following error when inserting:

    ERROR:  syntax error at or near "$2"
    LINE 1: SELECT $1 FROM $2
                   ^
    QUERY:  SELECT $1 FROM $2
    CONTEXT:  PL/pgSQL function worldmapkit.trgfn_keyvalue_insert() line 13 at EXECUTE statement

I have tried different variations of getting this value by using EXECUTE format() and a few other ways, but I am still not having any luck. Any help is appreciated!

tg_kyle · Accepted Answer · 2014-09-17 20:52:55Z

5

After much fiddling, I found the answer to my question. The correct syntax for the EXECUTE statement above is:

    EXECUTE format('SELECT $1.%I', key_column_name) 
    INTO key_column_value
    USING NEW;

This will get the column value of the NEW record. Hopefully, this will help out someone in a similar situation.

answered Sep 17, 2014 at 20:52

tg_kyle

815 bronze badges

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

1 Comment

Craig Ringer Over a year ago

Nicely done. I forgot support for this was added to PL/PgSQL.

Collectives™ on Stack Overflow

Postgres: How to get the value of a dynamic column from triggers

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related