CODEWITHSUNDEEP
sqlpostgresqltriggers
Mateusz Kowalski
Mateusz Kowalski

Reputation: 205

PostgreSQL trigger parametrized by attribute name

I want to create a trigger which will made selected attribute (given as a parameter) uppercase on first letter and lowercase on the remaining.

I would like to write it in similar way to this non-working code:

CREATE OR REPLACE FUNCTION myTrigger() RETURNS trigger AS
$BODY$
    DECLARE
        colname TEXT;
        newContent TEXT;
    BEGIN
        colname = TG_ARGV[0];
        newContent = format('SELECT ( CONCAT(UPPER(LEFT(NEW.%I,1)),LOWER(SUBSTRING(NEW.%I,2,LENGTH(NEW.%I)))) )', colname, colname, colname);
        EXECUTE newContent INTO format('NEW.%I', colname);
        RETURN NEW;
    END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Why isn't it possible to EXECUTE prepared statement inside of the trigger just like in the console?

Upvotes: 0

Views: 134

Answers (1)

Tom-db
Tom-db

Reputation: 6868

To dynamically update the NEW variable (this means, with the attribute name as variable) you can first build a hstore variable based on NEW, manipulate the hstore variable as you need and finally update the NEW variable with the populate_record function:

CREATE OR REPLACE FUNCTION myTrigger() RETURNS trigger AS
$BODY$
    DECLARE
        colname TEXT;
        newContent TEXT;
        new_record hstore;
BEGIN
colname = TG_ARGV[0];

    -- build a hstore from the NEW variable
    new_record= hstore(NEW);

    --fetch new content from hstore
    newContent = new_record -> colname;
    --updating the value
    newContent =  CONCAT(UPPER(LEFT(newContent,1)),LOWER(SUBSTRING(newContent,2,LENGTH(newContent)))) ;
    -- updating hstore content with new value
    new_record = new_record || hstore(colname , newContent);
    --updating the NEW variable with the populate_record function
    NEW = populate_record(NEW,new_record );
    RETURN  NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Upvotes: 1

Related Questions