Thread: cast record to array in plpgsql

dear list,

i'm trying to implement a general logging scheme with a plpgsql-trigger.
The idea is, that after an update the trigger compares the elements of
OLD and NEW and logs the changes made to the record columns. the trigger
should be usable by any table, so the field names are not known
beforehand. the natural solution seems to me to convert the records OLD,
NEW into 2-dimensional arrays where the second dimension is used to
address column-name and column_data and to step thru this array.
e.g. NEW{id=55;name='test'} would become a_new[1][1]='id;
a_new[1][2]=55; a_new[2][1]='name']; a_new[2][2] = 'test'.
as i think, that this should be quite a common problem, i would like to
ask whether some C-guru has already written a CAST-function for RECORD
to ARRAY, or has some better idea to solve this task.
thanks in advance, wz

2009/11/17 Kurt <wazkelzu@gmx.net>:
> dear list,
>
> i'm trying to implement a general logging scheme with a plpgsql-trigger.
> The idea is, that after an update the trigger compares the elements of
> OLD and NEW and logs the changes made to the record columns. the trigger
> should be usable by any table, so the field names are not known
> beforehand. the natural solution seems to me to convert the records OLD,
> NEW into 2-dimensional arrays where the second dimension is used to
> address column-name and column_data and to step thru this array.
> e.g. NEW{id=55;name='test'} would become a_new[1][1]='id;
> a_new[1][2]=55; a_new[2][1]='name']; a_new[2][2] = 'test'.
> as i think, that this should be quite a common problem, i would like to
> ask whether some C-guru has already written a CAST-function for RECORD
> to ARRAY, or has some better idea to solve this task.
> thanks in advance, wz


hello

look on hstore contrib module to CVS HEAD

http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html

It is for PostgreSQL 8.5, but I believe so code is portable to older versions.

Regards
Pavel Stehule
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>