pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedtrigger/function question
12+ messages / 5 participants
[nested] [flat]
* trigger/function question
@ 2001-05-13 21:57 Howard Williams <howieshouse@home.com>
0 siblings, 0 replies; 12+ messages in thread
From: Howard Williams @ 2001-05-13 21:57 UTC (permalink / raw)
To: pgsql-general
Hello there!
I need to perform a one-to-many insert based on the insert or update on a
table.
I'm not sure if it's possible without a loadable c module. I'm hoping it
is. Can't find a built-in looping mechanism.
I have tables A, B, and C. On every update or insert on A, I need to insert
a row in B for every row in C.
Here's a skeleton of my plan:
CREATE FUNCTION line_items_trig_func ( character varying(20), character
varying(20), character varying(50),
character varying(500), character varying(40), int2, int4, int4,
int4, character varying(20),
int2, timestamp, bool, character varying(20) ) returns boolean
as '' (some kind of for each in (select * from C) (insert intoB))"
LANGUAGE 'sql';
CREATE TRIGGER line_items_trigger AFTER INSERT or UPDATE on line_items
FOR EACH STATEMENT EXECUTE PROCEDURE ('col1', 'col2', ...);
Thanks,
howie
^ permalink raw reply [nested|flat] 12+ messages in thread
* Trigger Function question
@ 2023-07-10 18:20 DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
0 siblings, 1 reply; 12+ messages in thread
From: DAVID ROTH @ 2023-07-10 18:20 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
@ 2023-07-10 18:23 ` Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
0 siblings, 1 reply; 12+ messages in thread
From: Christophe Pettus @ 2023-07-10 18:23 UTC (permalink / raw)
To: DAVID ROTH <adaptron@comcast.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
@ 2023-07-10 18:29 ` DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
0 siblings, 1 reply; 12+ messages in thread
From: DAVID ROTH @ 2023-07-10 18:29 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 <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 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
@ 2023-07-10 18:31 ` Christophe Pettus <xof@thebuild.com>
2023-07-10 18:37 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:42 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
0 siblings, 2 replies; 12+ messages in thread
From: Christophe Pettus @ 2023-07-10 18:31 UTC (permalink / raw)
To: DAVID ROTH <adaptron@comcast.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
@ 2023-07-10 18:37 ` DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:38 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
1 sibling, 1 reply; 12+ messages in thread
From: DAVID ROTH @ 2023-07-10 18:37 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:37 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
@ 2023-07-10 18:38 ` Christophe Pettus <xof@thebuild.com>
2023-07-10 18:46 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
0 siblings, 1 reply; 12+ messages in thread
From: Christophe Pettus @ 2023-07-10 18:38 UTC (permalink / raw)
To: DAVID ROTH <adaptron@comcast.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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 query to find out which columns the table that cause the trigger to fire has.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:37 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:38 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
@ 2023-07-10 18:46 ` DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:51 ` Re: Trigger Function question Marcos Pegoraro <marcos@f10.com.br>
2023-07-10 18:52 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
0 siblings, 2 replies; 12+ messages in thread
From: DAVID ROTH @ 2023-07-10 18:46 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 query to find out which columns the table that cause the trigger to fire has.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:37 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:38 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:46 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
@ 2023-07-10 18:51 ` Marcos Pegoraro <marcos@f10.com.br>
1 sibling, 0 replies; 12+ messages in thread
From: Marcos Pegoraro @ 2023-07-10 18:51 UTC (permalink / raw)
To: DAVID ROTH <adaptron@comcast.net>; +Cc: Christophe Pettus <xof@thebuild.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:37 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:38 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:46 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
@ 2023-07-10 18:52 ` Christophe Pettus <xof@thebuild.com>
2023-07-11 19:41 ` Re: Trigger Function question Lorusso Domenico <domenico.l76@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Christophe Pettus @ 2023-07-10 18:52 UTC (permalink / raw)
To: DAVID ROTH <adaptron@comcast.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:37 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:38 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:46 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:52 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
@ 2023-07-11 19:41 ` Lorusso Domenico <domenico.l76@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Lorusso Domenico @ 2023-07-11 19:41 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: DAVID ROTH <adaptron@comcast.net>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.]
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Trigger Function question
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
@ 2023-07-10 18:42 ` DAVID ROTH <adaptron@comcast.net>
1 sibling, 0 replies; 12+ messages in thread
From: DAVID ROTH @ 2023-07-10 18:42 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2023-07-11 19:41 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-13 21:57 trigger/function question Howard Williams <howieshouse@home.com>
2023-07-10 18:20 Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:23 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:29 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:31 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:37 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:38 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-10 18:46 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
2023-07-10 18:51 ` Re: Trigger Function question Marcos Pegoraro <marcos@f10.com.br>
2023-07-10 18:52 ` Re: Trigger Function question Christophe Pettus <xof@thebuild.com>
2023-07-11 19:41 ` Re: Trigger Function question Lorusso Domenico <domenico.l76@gmail.com>
2023-07-10 18:42 ` Re: Trigger Function question DAVID ROTH <adaptron@comcast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox