PostgreSQL trigger parametrized by attribute name

Question

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?

Define "non-working"? What is the error message you get? What is the create trigger statement? — user330315
– user330315, Commented Jan 23, 2015 at 9:30
In INTO format(...) format is not a variable. Trigger statement is CREATE TRIGGER myTrigger BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE myTrigger('sname'); — Mateusz Kowalski
– Mateusz Kowalski, Commented Jan 23, 2015 at 9:36

Tom-db · Accepted Answer · 2015-01-23 11:43:15Z

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;

Collectives™ on Stack Overflow

PostgreSQL trigger parametrized by attribute name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related