Postgresql – Use a value for a Postgresql setting in a trigger

postgresqltransactiontrigger

We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them.

I'd like to be able to use SET LOCAL for this, because it will also serve as a useful check that a transaction has been properly created. So the idea is to insert a row in a requests table and then use SET LOCAL pubsubpull.request_id to set the ID for the request which can then be used in the INSERT statement in the update log table generated in the trigger (if available).

I can see what the value is using SHOW pubsubpull.request_id, but how do I capture that value so it can be used in the INSERT table? SHOW doesn't seem to be legal in SELECT in any way. These user defined options don't seem to show up in the pg_settings view so I can't select it from there.

I have looked at using txid_current() but would prefer not to need to.

Best Answer

You want current_setting:

SELECT current_setting('pubsubpull.request_id');

Related Solutions

Mysql – Error in creating trigger

I assume new.follower_id is supposed to be:

select follower_id from folow where circle_id=new.circle_id;

I don't think this will work. If you want to use this approach you need to declare a variable for follower_id and select into that. However, a better approach (IMO) is to do:

INSERT INTO activity( uid, performed_activity_id, activity_type_id ) 
    VALUES (new.uid, new.event_id,  '1');

insert into notification_table (sender_id,object_id,receiver_id)
    select new.uid ,new.event_id, follower_id 
    from folow where circle_id=new.circle_id;

MySQL Trigger – How to Convert Date to Word

Based on your comment, the trigger has multiple lines.

You need a BEGIN...END block as follows:

USE mcet
DELIMITER $$
DROP TRIGGER IF EXISTS tg_student_details_insert;
CREATE TRIGGER tg_student_details_insert BEFORE INSERT
ON student_details FOR EACH ROW
BEGIN
    SET NEW.created_date = NOW();
    SET NEW.dobw = date_to_words(NEW.dob);
END;
DELIMITER ;

You answered this question before (How to autoincrement Prod_id based on Product_Name using trigger in mysql). In that post, you used a BEGIN...END block for four(4) lines of code. You needed one for this post as well.

Best Answer

Related Solutions

Mysql – Error in creating trigger

MySQL Trigger – How to Convert Date to Word

Related Question