Обсуждение: Trigger Function question
In a trigger function, is there a way to get a list of all of the columns in the triggering table?
I want to be able to use a single trigger function with multiple tables.
Thanks
> On Jul 10, 2023, at 11:20, DAVID ROTH <adaptron@comcast.net> wrote: > > In a trigger function, is there a way to get a list of all of the columns in the triggering table? You can get the table that the trigger fired on with TG_TABLE_SCHEMA and TG_TABLE_NAME, and then query the system catalogsto get a list of columns. But can you tell us a bit more about your use-case? You may be able to write the trigger in such a way that it doesn't needto change behavior based on the columns.
I want to use a single trigger function to log multiple tables and the tables have different columns. I can get the namesof the columns from the catalog. But I have not been able to figure out how to get NEW.x when x is not known untilrun time. > On 07/10/2023 2:23 PM EDT Christophe Pettus <xof@thebuild.com> wrote: > > > > On Jul 10, 2023, at 11:20, DAVID ROTH <adaptron@comcast.net> wrote: > > > > In a trigger function, is there a way to get a list of all of the columns in the triggering table? > > You can get the table that the trigger fired on with TG_TABLE_SCHEMA and TG_TABLE_NAME, and then query the system catalogsto get a list of columns. > > But can you tell us a bit more about your use-case? You may be able to write the trigger in such a way that it doesn'tneed to change behavior based on the columns.
> On Jul 10, 2023, at 11:29, DAVID ROTH <adaptron@comcast.net> wrote: > > I want to use a single trigger function to log multiple tables and the tables have different columns. I can get the namesof the columns from the catalog. But I have not been able to figure out how to get NEW.x when x is not known untilrun time. Unless you only want to log a subset of rows from each table, it's not required that you get the specific columns. Here'san example of how to do a generic auditing trigger: https://wiki.postgresql.org/wiki/Audit_trigger If it's supported on your platform, you might also look at the pg_audit extension.
Thanks for the example. I have a test trigger now that does that but my application needs all of the columns. > On 07/10/2023 2:31 PM EDT Christophe Pettus <xof@thebuild.com> wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH <adaptron@comcast.net> wrote: > > > > I want to use a single trigger function to log multiple tables and the tables have different columns. I can get thenames of the columns from the catalog. But I have not been able to figure out how to get NEW.x when x is not known untilrun time. > > Unless you only want to log a subset of rows from each table, it's not required that you get the specific columns. Here'san example of how to do a generic auditing trigger: > > https://wiki.postgresql.org/wiki/Audit_trigger > > If it's supported on your platform, you might also look at the pg_audit extension.
> On Jul 10, 2023, at 11:37, DAVID ROTH <adaptron@comcast.net> wrote: > > Thanks for the example. I have a test trigger now that does that but my application needs all of the columns. I'm not quite sure I understanding. Logging NEW.* and OLD.* *does* get all the columns, without having to specific queryto find out which columns the table that cause the trigger to fire has.
I was hoping that NEW could be treated as a record or as an arrayy similar to pg_argv. > On 07/10/2023 2:31 PM EDT Christophe Pettus <xof@thebuild.com> wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH <adaptron@comcast.net> wrote: > > > > I want to use a single trigger function to log multiple tables and the tables have different columns. I can get thenames of the columns from the catalog. But I have not been able to figure out how to get NEW.x when x is not known untilrun time. > > Unless you only want to log a subset of rows from each table, it's not required that you get the specific columns. Here'san example of how to do a generic auditing trigger: > > https://wiki.postgresql.org/wiki/Audit_trigger > > If it's supported on your platform, you might also look at the pg_audit extension.
I saw your message about a "few" columns and missed the new.* notation. Is there a way to get new.* into a jsonb column? > On 07/10/2023 2:38 PM EDT Christophe Pettus <xof@thebuild.com> wrote: > > > > On Jul 10, 2023, at 11:37, DAVID ROTH <adaptron@comcast.net> wrote: > > > > Thanks for the example. I have a test trigger now that does that but my application needs all of the columns. > > I'm not quite sure I understanding. Logging NEW.* and OLD.* *does* get all the columns, without having to specific queryto find out which columns the table that cause the trigger to fire has.
Is there a way to get new.* into a jsonb column?
select json_object_agg(js.key, js.value) from json_each_text(row_to_json(new.*)) js
Marcos
> On Jul 10, 2023, at 11:46, DAVID ROTH <adaptron@comcast.net> wrote: > > Is there a way to get new.* into a jsonb column? The to_jsonb() function accepts a row type like NEW.*, and returns a JSONB object with the keys as column names.
I've just finish to do the same thing.
For my needs, I decided to create a table with the information I need for each view (yes I set trigger on view not on table).
anyway, hstore is more performant rather than jsonb but both of them could treat NEW and OLD as hashmap (or associative array)
Il giorno lun 10 lug 2023 alle ore 20:53 Christophe Pettus <xof@thebuild.com> ha scritto:
> On Jul 10, 2023, at 11:46, DAVID ROTH <adaptron@comcast.net> wrote:
>
> Is there a way to get new.* into a jsonb column?
The to_jsonb() function accepts a row type like NEW.*, and returns a JSONB object with the keys as column names.
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]