Thread: Testing if a Column Exists in a NEW record of a Trigger Function
Dear List,
As part of the Audit process in my psql database, I have a standard set of columns "InsertBy", "InsertTime", "LastUpdateBy" & "LastUpdateTme". I use a standard Trigger function [listed below] to ensure the correct details are added to each record.
However, on some data tables where data is only imported and not updated, the "Update" columns are not required.
As such, is there a way to test the NEW record variable to see if the column "LastUpdateBy" exists? I could not find any information in the manual to describe how this might be done.
Thanks
Simon
CREATE OR REPLACE FUNCTION "Security"."InsertStamp"() RETURNS trigger as $InsertStamp$
DECLARE
UserID integer;
BEGIN
SELECT "IDUser" INTO UserID FROM "Security"."User" WHERE "Username" = current_user;
NEW."InsertBy" := UserID;
NEW."InsertTime" := current_timestamp;
NEW."LastUpdateBy" := UserID;
NEW."LastUpdateTime" := current_timestamp;
RETURN NEW;
END;
$InsertStamp$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
As part of the Audit process in my psql database, I have a standard set of columns "InsertBy", "InsertTime", "LastUpdateBy" & "LastUpdateTme". I use a standard Trigger function [listed below] to ensure the correct details are added to each record.
However, on some data tables where data is only imported and not updated, the "Update" columns are not required.
As such, is there a way to test the NEW record variable to see if the column "LastUpdateBy" exists? I could not find any information in the manual to describe how this might be done.
Thanks
Simon
CREATE OR REPLACE FUNCTION "Security"."InsertStamp"() RETURNS trigger as $InsertStamp$
DECLARE
UserID integer;
BEGIN
SELECT "IDUser" INTO UserID FROM "Security"."User" WHERE "Username" = current_user;
NEW."InsertBy" := UserID;
NEW."InsertTime" := current_timestamp;
NEW."LastUpdateBy" := UserID;
NEW."LastUpdateTime" := current_timestamp;
RETURN NEW;
END;
$InsertStamp$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
In trigger functions you will have access to table and schema name who invoked the trigger. I guess you can query for the column name from catalogs.
Regards
Sameer
PS: Sent from my Mobile device. Pls ignore typo n abb
On Mon, Jan 6, 2014 at 8:15 PM, Simon ... <wedgetail_e@hotmail.com> wrote: > As part of the Audit process in my psql database, I have a standard set of > columns "InsertBy", "InsertTime", "LastUpdateBy" & "LastUpdateTme". I use a > standard Trigger function [listed below] to ensure the correct details are > added to each record. > However, on some data tables where data is only imported and not updated, > the "Update" columns are not required. > > As such, is there a way to test the NEW record variable to see if the column > "LastUpdateBy" exists? I could not find any information in the manual to > describe how this might be done. It can be done by installing the hstore [1] extension and checking it as hstore(NEW) ? 'LastUpdateBy' [1] http://www.postgresql.org/docs/9.3/interactive/hstore.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray.ru@gmail.com