Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
01.05.2011 12:58, Basil Bourque wrote: Hoorah! I was able to complete my single PL/pgSQL function to create history records tracking individual field value changes generically for all my tables. Some developers call this an audit trail, though an accountant might say otherwise. I made auditing based on triggers like aforementioned. And now I need fill audit table with already presented data. But there is a problem. within trigger EXECUTE 'SELECT ($1).name::text' INTO newVal USING NEW; works fine but function (table decor has field name) CREATE OR REPLACE FUNCTION odb_InitLog() RETURNS void AS DECLARE obj record; BEGIN FOR obj IN (SELECT * FROM decor) LOOP EXECUTE 'SELECT ($1).name::text' INTO newVal USING obj; END LOOP; END; doesn't work - ERROR: could not identify column name in record data type Why? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
On Wed, Aug 24, 2011 at 1:03 PM, ivan_14_32 ivan_14...@mail.ru wrote: 01.05.2011 12:58, Basil Bourque wrote: Hoorah! I was able to complete my single PL/pgSQL function to create history records tracking individual field value changes generically for all my tables. Some developers call this an audit trail, though an accountant might say otherwise. I made auditing based on triggers like aforementioned. And now I need fill audit table with already presented data. But there is a problem. within trigger EXECUTE 'SELECT ($1).name::text' INTO newVal USING NEW; works fine but function (table decor has field name) CREATE OR REPLACE FUNCTION odb_InitLog() RETURNS void AS DECLARE obj record; BEGIN FOR obj IN (SELECT * FROM decor) LOOP EXECUTE 'SELECT ($1).name::text' INTO newVal USING obj; END LOOP; END; doesn't work - ERROR: could not identify column name in record data type folks, this (dynamic field access of generic record in plpgsql) is the number one FAQ on this list. please check the archives before searching (not picking on you specifically, it just gets asked in some variant an awful lot). First point: hstore execute. if you _must_ use execute, you have to cast at some point. when you pass a record to something, it doesn't have the necessary context to know the field names. In your case, though, an explicit composite type is the way to go: DECLARE obj decor; BEGIN FOR obj IN SELECT * FROM decor LOOP newVal := obj.name; END LOOP; END; merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
On 04/28/2011 10:46 PM, Basil Bourque wrote: In PL/pgSQL, how does one generically access the fields of the OLD or NEW record? I've tried code such as this: 'NEW.' || quote_ident( myColumnNameVar ) || '::varchar' But when run by an EXECUTE command, I get errors such as: ERROR: missing FROM-clause entry for table old SQL state: 42P01 It seems that I cannot get PL/pgSQL to interpret the text of NEW. + column name as text. My goal is to loop each field in a trigger, comparing the OLD. NEW. values of each field. If different I want to log both values in a history/audit-trail table. Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to get an array of fields from the Record. My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute, pg_class, pg_type). But I cannot get NEW. || colNameVar to be interpreted. Perhaps there is a better approach. If anyone is curious, my source code is pasted below. --Basil Bourque We use plpythonu for this as the new and old structures are dictionaries. Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
Hoorah! I was able to complete my single PL/pgSQL function to create history
records tracking individual field value changes generically for all my tables.
Some developers call this an audit trail, though an accountant might say
otherwise.
Thanks for all the replies.
Special thanks to John DeSoi for pointing me to a crucial code example to make
PL/pgSQL interpret:
OLD. || myColumnNameVar
as:
OLD.first_name(for example)
The crucial line is:
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
found here:
http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
I'm new to SQL and Postgres, so I may be wrong but my interpretation of this is:
Since there appears no way to make the PL/pgSQL interpreter interpret our
desired string, the trick is to look outside of PL/pgSQL and instead use the
SQL interpreter. Brilliant! It works, and it seems to be fast enough, at least
for my needs.
I'll share my current code table structure below. Caveat: This code has not
yet been thoroughly tested, nor has it been deployed. I only finalized it
today.
--Basil Bourque
[A] I'm working on a greenfield project, where:
• I've built every table to have a primary key of type uuid named exactly
pkey_.
• Every table has a TIMESTAMPTZ field named record_modified_.
My approach below hard-codes these assumptions.
[B] I have this history_ table:
CREATE TABLE history_
(
pkey_ uuid NOT NULL DEFAULT uuid_generate_v1mc(), -- The primary key for this
table, though no primary key constraint was created (for the sake of
performance and conservation). This column and timestamp_ column are the only
two columns about this table itself. All other columns are about the
inserted/modified/deleted record in some other table.
table_name_ character varying(120) NOT NULL, -- Name of table whose row is
being affected (inserted, deleted, or modified).
column_name_ character varying(120) NOT NULL, -- Name of the column in some
other table whose row value is being modified. This column's value is empty
string if the operation was DELETE.
timestamp_ timestamp with time zone NOT NULL DEFAULT clock_timestamp(), --
The moment this record was created. Using the clock_timestamp() function as a
default, to capture the actual moment in time rather than moment when
transaction began.
db_user_name_ character varying(120) NOT NULL DEFAULT current_user(), --
The name of the Postgres user logged in to this database connection/session.
app_name_ character varying(120) NOT NULL DEFAULT
current_setting('application_name'::text), -- The name of the application
connected to the database. May also include the version number of app, and the
name of the human user authenticated within the app.
old_value_ character varying(120) NOT NULL DEFAULT ''::character varying,
new_value_ character varying(120) NOT NULL DEFAULT ''::character varying,
uuid_ uuid NOT NULL, -- The UUID of the row being affected, the row being
inserted, updated, or deleted. Assumes every table whose history is being
recorded uses the 'uuid' data type as its primary key.
operation_ character varying(120) NOT NULL, -- What database operation
resulted in this trigger running: INSERT, UPDATE, DELETE, or TRUNCATE.
table_oid_ oid NOT NULL, -- The oid of the table whose record is being
modified. May be helpful if a table name changes over time.
ordinal_position_of_column_ integer NOT NULL, -- The position of the affected
column in its table. Every new column gets a number, incremented by one for
each. This may be helpful when analyzing changes across a stretch of time
during which a column's name was changed. Apparently columns have no oid, so we
are recording this number instead.
transaction_began_ timestamp with time zone NOT NULL DEFAULT
transaction_timestamp() -- The time when the current transaction began. Can act
like a transaction identifier, to group multiple history_ rows of the same
transaction together. This is not foolproof, as multiple transaction could
possibly start in the same split second moment. Assuming the computer's clock
has a fine resolution, this chance of a coincidence should be quite miniscule.
)
I do not have a primary constraint for this table. The pkey_ column acts as a
primary key, but there is no need for an index or uniqueness testing for this
special table.
[C] For every table I want to track field-level value changes, I create a
trigger like this:
CREATE TRIGGER XXX_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON XXX_
FOR EACH ROW EXECUTE PROCEDURE make_history_();
where 'XXX' is the name of the table.
[D] I created this function:
CREATE OR REPLACE FUNCTION make_history_()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
/* Purpose: Make a history of changes to most fields in the table calling
this trigger function.
This kind of history tracking is also known as an audit trail.
This function works by detecting each change in
[GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
In PL/pgSQL, how does one generically access the fields of the OLD or NEW record? I've tried code such as this: 'NEW.' || quote_ident( myColumnNameVar ) || '::varchar' But when run by an EXECUTE command, I get errors such as: ERROR: missing FROM-clause entry for table old SQL state: 42P01 It seems that I cannot get PL/pgSQL to interpret the text of NEW. + column name as text. My goal is to loop each field in a trigger, comparing the OLD. NEW. values of each field. If different I want to log both values in a history/audit-trail table. Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to get an array of fields from the Record. My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute, pg_class, pg_type). But I cannot get NEW. || colNameVar to be interpreted. Perhaps there is a better approach. If anyone is curious, my source code is pasted below. --Basil Bourque CREATE OR REPLACE FUNCTION table_make_history_() RETURNS trigger AS $BODY$ DECLARE metadata_record RECORD; /* http://www.postgresql.org/docs/current/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS */ table_dot_column VARCHAR; my_sql VARCHAR; column_is_loggable_var BOOLEAN; edited_var BOOLEAN; BEGIN IF (TG_OP = 'INSERT') THEN -- Ignore this case ELSIF (TG_OP = 'UPDATE') THEN /* Get a list of column name, data type, and position with in table. attname = Name of column. atttypid = Data type of column (as an oid from pg_type.oid) */ FOR metadata_record IN SELECT attname::varchar AS nth_col_name, pg_type.typname::varchar as nth_col_type, pg_attribute.attnum FROM pg_attribute, pg_class, pg_type WHERE attrelid = pg_class.oid AND pg_attribute.attisdropped = False AND relname = TG_TABLE_NAME AND attnum 0 AND atttypid = pg_type.oid LOOP -- For each table in the table calling this trigger. -- Now metadata_record has one record from resultset of SELECT query above. --table_dot_column := TG_TABLE_NAME::VARCHAR || '.' || metadata_record.nth_col_name ; column_is_loggable_var := position( '_x_' in metadata_record.nth_col_name ) 1 ; IF column_is_loggable_var THEN -- The name of column in question does NOT contain _x_. So, proceed to possibly log modified data. -- See if the NEW-OLD values are different. edited_var := true; -- Next line fails. --EXECUTE 'edited_var := ( OLD.' || quote_ident(metadata_record.nth_col_name) || ' = NEW.' || quote_ident(metadata_record.nth_col_name) || ' );' ; PERFORM 'edited_var := ( OLD.' || quote_ident(metadata_record.nth_col_name) || ' NEW.' || quote_ident(metadata_record.nth_col_name) || ' );' ; IF edited_var THEN EXECUTE 'INSERT INTO history_ ( operation_, table_oid_, table_name_, uuid_, column_name_, old_value_, new_value_ ) ' || 'VALUES ( ' || quote_literal(TG_OP) || ', ' || TG_RELID || ', ' || quote_literal(TG_TABLE_NAME) || ', ' || quote_literal(OLD.pkey_) || '::uuid, ' || quote_literal(metadata_record.nth_col_name) || ', OLD.' || quote_ident(metadata_record.nth_col_name) || '::varchar' || ', NEW.' || quote_ident(metadata_record.nth_col_name) || '::varchar' || ' ); ' ; END IF; END IF; END LOOP; RETURN NEW; ELSIF (TG_OP = 'DELETE') THEN -- ignore this case END IF; RETURN NULL; /* Should never reach this point. Perhaps we should raise an error here. */ END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
On Thu, Apr 28, 2011 at 12:46:50PM -0700, Basil Bourque wrote: In PL/pgSQL, how does one generically access the fields of the OLD or NEW record? I've tried code such as this: 'NEW.' || quote_ident( myColumnNameVar ) || '::varchar' But when run by an EXECUTE command, I get errors such as: ERROR: missing FROM-clause entry for table old SQL state: 42P01 It seems that I cannot get PL/pgSQL to interpret the text of NEW. + column name as text. My goal is to loop each field in a trigger, comparing the OLD. NEW. values of each field. If different I want to log both values in a history/audit-trail table. Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to get an array of fields from the Record. My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute, pg_class, pg_type). But I cannot get NEW. || colNameVar to be interpreted. Perhaps there is a better approach. long story short - it's not possible. a bit longer story: there are couple of workarounds. 1. you can use hstore datatype and it's casts from record to hstore 2. you can use another pl/* language - like pl/perl - which doesn't have this problem 3. you can use ready made tool for auditing that does what you want, so you don't have to worry ( http://pgfoundry.org/projects/tablelog/ ) 4. http://www.depesz.com/index.php/2010/03/10/dynamic-updates-of-fields-in-new-in-plpgsql/ but really, read, and understand the warnings. depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OLD. || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records)
On Apr 28, 2011, at 3:46 PM, Basil Bourque wrote: It seems that I cannot get PL/pgSQL to interpret the text of NEW. + column name as text. My goal is to loop each field in a trigger, comparing the OLD. NEW. values of each field. If different I want to log both values in a history/audit-trail table. Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to get an array of fields from the Record. This might help you: http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
