Re: Trigger Function question
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 ha scritto: > > > > On Jul 10, 2023, at 11:46, DAVID ROTH 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.]
Re: Trigger Function question
> On Jul 10, 2023, at 11:46, DAVID ROTH 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.
Re: Trigger Function question
> > 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
Re: Trigger Function question
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 wrote: > > > > On Jul 10, 2023, at 11:37, DAVID ROTH 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 query to find out which columns the > table that cause the trigger to fire has.
Re: Trigger Function question
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 wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger function to log multiple tables and the > > tables have different columns. I can get the names 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 until run 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's an 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.
Re: Trigger Function question
> On Jul 10, 2023, at 11:37, DAVID ROTH 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 query to find out which columns the table that cause the trigger to fire has.
Re: Trigger Function question
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 wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger function to log multiple tables and the > > tables have different columns. I can get the names 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 until run 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's an 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.
Re: Trigger Function question
> On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > I want to use a single trigger function to log multiple tables and the tables > have different columns. I can get the names 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 > until run 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's an 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.
Re: Trigger Function question
I want to use a single trigger function to log multiple tables and the tables have different columns. I can get the names 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 until run time. > On 07/10/2023 2:23 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:20, DAVID ROTH 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 catalogs to 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 need to change behavior based on > the columns.
Re: Trigger Function question
> On Jul 10, 2023, at 11:20, DAVID ROTH 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 catalogs to 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 need to change behavior based on the columns.
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
