Re: Context variable in application and trigger code

On 9/28/24 14:37, yudhi s wrote: Hi, We have a trigger function called from a trigger which executes before inserting rows in the table (say TAB1). This trigger function does some conversion of code to description and persists the description in the table in respective columns. We want to

Context variable in application and trigger code

Hi, We have a trigger function called from a trigger which executes before inserting rows in the table (say TAB1). This trigger function does some conversion of code to description and persists the description in the table in respective columns. We want to keep this trigger as light as possible as

Re: Manual query vs trigger during data load

On 2024-09-14 21:21:45 +0530, yudhi s wrote: > On Sat, Sep 14, 2024 at 4:17 PM Peter J. Holzer wrote: > On 2024-09-14 00:54:49 +0530, yudhi s wrote: > > As "thiemo" mentioned , it can be done as below method, but if > > we have multiple lookup tables to be populated for multiple >

Re: Manual query vs trigger during data load

On Sat, Sep 14, 2024 at 4:17 PM Peter J. Holzer wrote: > On 2024-09-14 00:54:49 +0530, yudhi s wrote: > > As "thiemo" mentioned , it can be done as below method, but if we have > > multiple lookup tables to be populated for multiple columns , then , how > can > > the INSERT query be tweaked to ca

Re: Manual query vs trigger during data load

On 2024-09-14 00:54:49 +0530, yudhi s wrote: > As "thiemo" mentioned , it can be done as below method, but if we have > multiple lookup tables to be populated for multiple columns , then , how can > the INSERT query be tweaked to cater the need here? Just use a join: insert into target(val1, v

Re: Manual query vs trigger during data load

On Fri, Sep 13, 2024 at 8:27 PM Adrian Klaver wrote: > On 9/13/24 07:50, Adrian Klaver wrote: > > On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: > >> Hello, I find it unlikely that the trigger will work properly, since > >> the reserved fields of the OLD

Re: Manual query vs trigger during data load

> On Sep 13, 2024, at 10:57 AM, Adrian Klaver wrote: > > On 9/13/24 07:50, Adrian Klaver wrote: >>> On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: >>> Hello, I find it unlikely that the trigger will work properly, since the >>> reserved fi

Re: Manual query vs trigger during data load

On 9/13/24 07:50, Adrian Klaver wrote: On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: Hello, I find it unlikely that the trigger will work properly, since the reserved fields of the OLD subset have no value in an INSERT I'm not seeing that the OP is asking for OLD.* values,

Re: Manual query vs trigger during data load

On 9/13/24 02:58, Juan Rodrigo Alejandro Burgos Mella wrote: Hello, I find it unlikely that the trigger will work properly, since the reserved fields of the OLD subset have no value in an INSERT I'm not seeing that the OP is asking for OLD.* values, they are just looking to include the r

Re: Manual query vs trigger during data load

Hello, I find it unlikely that the trigger will work properly, since the reserved fields of the OLD subset have no value in an INSERT Atte JRBN El vie, 13 de sept de 2024, 04:32, yudhi s escribió: > Hello All, > > We are having a table which is going to be inserted with 100's of

Manual query vs trigger during data load

Hi To me it seems possible to create an insert select, e.g. Insert into tab1 (val1, val2) Select valA, valB >From tab2 Where valC = :param1 Cheers Thiemo

Manual query vs trigger during data load

ed to understand ,is it possible to do it along with the INSERT query or is it better to have a trigger created for the same? For. e.g Below is the current Insert query used in the Java code. We want to fetch the value for "column2" from a lookup table rather than directly inserting as it&#x

Re: Trigger usecase

d we cater this inside the > apache flink application code or should we handle this using trigger on the > table level which will execute on each INSERT and execute this logic? > > I understand trigger is difficult to debug and monitor stuff. But here in > this case , team mates

Re: Trigger usecase

On 7/30/24 13:28, David G. Johnston wrote: On Tue, Jul 30, 2024 at 11:46 AM sud > wrote: Not sure of the exact pros and cons, but we were following certain rules like , if it's business logic which needs to be implemented in Database, then it should not be

Re: Trigger usecase

On Tue, Jul 30, 2024 at 11:46 AM sud wrote: > > Not sure of the exact pros and cons, but we were following certain rules > like , if it's business logic which needs to be implemented in Database, > then it should not be done using triggers but rather should be done through > database procedure/fu

Re: Trigger usecase

On 7/30/24 11:46, sud wrote: On Tue, Jul 30, 2024 at 10:54 PM Laurenz Albe <mailto:laurenz.a...@cybertec.at>> wrote: It is largely a matter of taste. The advantage of a trigger is that it works even if somebody bypasses the application to insert data. I t

Re: Trigger usecase

On Tue, Jul 30, 2024 at 10:54 PM Laurenz Albe wrote: > > It is largely a matter of taste. > > The advantage of a trigger is that it works even if somebody bypasses the > application > to insert data. > > I think that triggers are easy to debug, but again, that's a m

Re: Trigger usecase

e old data with a flag > as > inactive and the new record should get inserted with flag as active. So for > such > use case , should we cater this inside the apache flink application code or > should > we handle this using trigger on the table level which will execute on each

Re: Trigger usecase

On Tue, Jul 30, 2024 at 8:16 AM sud wrote: > > I understand, technically its possible bith the way, but want to > understand experts opinion on this and pros ans cons? > > Have client code call a function that performs the relevant work directly instead of having a trigger perfo

Trigger usecase

want to modify or override the existing customer data but want to keep the old data with a flag as inactive and the new record should get inserted with flag as active. So for such use case , should we cater this inside the apache flink application code or should we handle this using trigger on the

Re: Syntax on BEFORE Trigger - Cascade?

se demonstrating that fact so it can be investigated/explained. What version are you running? > There aren't any delete triggers for either table. Any idea why this isn't > working? Does cascade function differently in Postgres? > > Nope (to both) > > I've also tried

Re: Syntax on BEFORE Trigger - Cascade?

David Barbour writes: > The files are appropriately deleted in Oracle, but Postgres is returning > the following: > *ERROR: Attempt to suppress referential action with before trigger. > CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1 >

Syntax on BEFORE Trigger - Cascade?

_file FOREIGN KEY (import_job_oid) REFERENCES idev.import_job (oid) MATCH SIMPLEON UPDATE NO ACTIONON DELETE CASCADE;* The files are appropriately deleted in Oracle, but Postgres is returning the following: *ERROR: Attempt to suppress referential action with before trigger. CONTEXT: SQL stat

Re: Does trigger only accept functions?

On Tue, 11 Jun 2024 at 18:25, Ron Johnson wrote: Since all the functions are going to be similar, I'd write a shell script > to generate all the triggers, one per relevant. If you're going to record > every field, then save effort, and don't bother enumerating them. You'll > need to dig into th

Re: Does trigger only accept functions?

Best regards, >> >> depesz >> >> > Thank you so much. I hope you mean something as below when you say making > it dynamic. Because we have the audit tables having more number of columns > as compared to the source table and for a few the column name is a bit > differ

Re: Does trigger only accept functions?

nerated always as identity primary key, source_schema text, source_table text, deleting_user text, deleted_at timestamptz, deleted_row hstore ); create function log_deletes() returns trigger as $$ DECLARE BEGIN INSERT INTO deleted_rows (source_schema, source_table, deleting_user

Re: Does trigger only accept functions?

CREATE OR REPLACE FUNCTION log_deletes() RETURNS TRIGGER AS $$ DECLARE     audit_table_name TEXT;     audit_query TEXT; BEGIN     IF TG_TABLE_NAME = 'source_table1' THEN         audit_table_name := 'delete_audit1';         audit_query := 'INSERT INTO ' || audit_table_name

Re: Does trigger only accept functions?

> depesz > > My apology, if interpreting it wrong way. It doesn't make much difference though, but do you mean something like below? CREATE OR REPLACE FUNCTION log_deletes() RETURNS TRIGGER AS $$ DECLARE audit_table_name TEXT; audit_query TEXT; BEGIN IF TG_TABLE_NAME = 

Re: Does trigger only accept functions?

On Wed, Jun 12, 2024 at 12:19:55AM +0530, veem v wrote: > CREATE OR REPLACE FUNCTION log_deletes() > RETURNS TRIGGER AS $$ > BEGIN > IF TG_TABLE_NAME = 'source_table1' THEN > INSERT INTO delete_audit1 ( col1, col2, col3) > VALUES (OLD.col1, OLD.

Re: Does trigger only accept functions?

amic. Because we have the audit tables having more number of columns as compared to the source table and for a few the column name is a bit different. -- Trigger for deletes CREATE TRIGGER before_delete BEFORE DELETE ON source_table FOR EACH ROW EXECUTE FUNCTION log_deletes(); -- Trigger for source_tab

Re: Does trigger only accept functions?

On Tue, Jun 11, 2024 at 12:47:14AM +0530, veem v wrote: > to be called from ~50 triggers? or any other better approach exists to > handle this? pgaudit extension? Or just write all the changes to single table? Or use dynamic queries that will build the insert based on the name of table the event

Re: Does trigger only accept functions?

On Mon, 2024-06-10 at 13:20 -0700, Adrian Klaver wrote: > https://www.postgresql.org/docs/15/sql-createtrigger.html > > That is either a function or a procedure. The trigger function must be a function, it cannot be a procedure. The syntax EXECUTE PROCEDURE is just for backward comp

Re: Does trigger only accept functions?

fired on delete which will insert records into the audit table. But I see the trigger is not accepting the insert query directly, rather it's asking to call a function and to put the business logic inside that function, something as below. So does that mean, to enable audit on the ~50

Does trigger only accept functions?

cords into the audit table. But I see the trigger is not accepting the insert query directly, rather it's asking to call a function and to put the business logic inside that function, something as below. So does that mean, to enable audit on the ~50 base table , we will have ~50 functions to

Re: error in trigger creation

On 4/21/24 14:21, Tom Lane wrote: Adrian Klaver writes: On 4/21/24 11:20, yudhi s wrote: So in this case i was wondering if "event trigger" can cause any additional threat and thus there is no such privilege like "create trigger" exist in postgres and so it should be treat

Re: error in trigger creation

Adrian Klaver writes: > On 4/21/24 11:20, yudhi s wrote: >> So in this case i was wondering if "event trigger" can cause any >> additional threat and thus there is no such privilege like "create >> trigger" exist in postgres and so it should be treated 

Re: error in trigger creation

On 4/21/24 11:20, yudhi s wrote: On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote: So do you mean , we should not create the event trigger using the "security definer" , rather have the super user do this each time we have to creat

Re: error in trigger creation

child table? > Yes all the child tables have foreign keys indexed. Again I don't want to divert the main topic(trigger creation) . I will provide the exact test case how it puts exclusive lock and runs longer as scans all child partitions (which must be using indexes though) but as the

Re: error in trigger creation

On Sun, Apr 21, 2024 at 2:58 PM yudhi s wrote: > the partition drop from parent is taking longer as it scans all the > partitions of the child table > Does the relevant supporting index exist on the child table?

Re: error in trigger creation

s using pg_partman, then create the below event trigger which will add the foreign key to the new partitions for all new future partitions. And we are planning to create such an event trigger for all such child tables that are partitioned and having FK's. CREATE OR REPLACE FUNCTION add_pa

Re: error in trigger creation

te the >> >> function from super user and then execute the grant? But doesn't that >> mean, >> >> each time we want to create a new event trigger we have to be again >> >> dependent on the "super user" to modify the security definer function? &g

Re: error in trigger creation

t doesn't that > mean, > >> each time we want to create a new event trigger we have to be again > >> dependent on the "super user" to modify the security definer function? > > > Dynamic SQL. See “execute” in plpgsql. > > You might as well just g

Re: error in trigger creation

< >>> david.g.johns...@gmail.com> wrote: >>> >>>> On Sunday, April 21, 2024, yudhi s wrote: >>>> >>>>> so that it will be able to assign the privilege, so we will be able to >>>>> create the event trigger without need to run the ev

Re: error in trigger creation

21, 2024, yudhi s wrote: >>> >>>> so that it will be able to assign the privilege, so we will be able to >>>> create the event trigger without need to run the event trigger script from >>>> super user itself? >>>> >>> >>&g

Re: error in trigger creation

"David G. Johnston" writes: > On Sunday, April 21, 2024, yudhi s wrote: >> Are you saying something like below, in which we first create the >> function from super user and then execute the grant? But doesn't that mean, >> each time we want to create a n

Re: error in trigger creation

On Sunday, April 21, 2024, yudhi s wrote: > On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sunday, April 21, 2024, yudhi s wrote: >> >>> so that it will be able to assign the privilege, so we will be able to

Re: error in trigger creation

On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sunday, April 21, 2024, yudhi s wrote: > >> so that it will be able to assign the privilege, so we will be able to >> create the event trigger without need to run the event trig

Re: error in trigger creation

On Sunday, April 21, 2024, yudhi s wrote: > so that it will be able to assign the privilege, so we will be able to > create the event trigger without need to run the event trigger script from > super user itself? > Write a security-definer function owned by superuser and gr

error in trigger creation

Hi All, We are seeing privilege issues while creating event triggers. It says the user "*must be a superuser to create an event trigger*". So my question is , if we have application user as "app_user" which is responsible for creating database objects in schema "app_sch

Re: Question on trigger

> (mainly for each row trigger) created on them. > > And also the bulk DML/array based insert (which inserts multiple rows in > one short or one batch) , in those cases it seems the trigger will not > make that happen as it will force it to make it hap

Re: Question on trigger

On Sat, 13 Apr 2024 at 21:44, Adrian Klaver wrote: > On 4/13/24 00:03, veem v wrote: > > Thank you Adrian. > > > > So it seems the heavy DML tables will see an impact if having triggers > > (mainly for each row trigger) created on them. > > > > And also

Re: Question on trigger

On 4/13/24 00:03, veem v wrote: Thank you Adrian. So it seems the heavy DML tables will see an impact if having triggers (mainly for each row trigger) created on them. And also the bulk DML/array based insert (which inserts multiple rows in one short or one batch) , in those cases it seems

Re: Question on trigger

Thank you Adrian. So it seems the heavy DML tables will see an impact if having triggers (mainly for each row trigger) created on them. And also the bulk DML/array based insert (which inserts multiple rows in one short or one batch) , in those cases it seems the trigger will not make that happen

Re: Question on trigger

downside of such audit trigger setup for all the tables? Will it impact the bulk data insert/update/delete OR slowdown of any of the DML operations significantly (and thus will not be advisable to use for all tables but selected ones)? Triggers are overhead in Postgres as they where in Oracle. If

Question on trigger

Hi, We used to use Oracle database in which we had audit triggers(something as below) mandated for all tables by the control team. Now we are going to use the postgresql 15.4 database for one of our applications. So,wanted to understand if there exists any downside of such audit trigger setup for

Re: Me again with an insert trigger problem

On 2/27/24 14:38, Thiemo Kellner wrote: Am 27.02.2024 um 23:20 schrieb Adrian Klaver: I am not sure, what you want me to show with your test case. And I am not sure whether I could not make myself clear. Please bear with me if I try to make things clearer with an example. Your comment wa

Re: Me again with an insert trigger problem

Am 27.02.2024 um 23:20 schrieb Adrian Klaver: On 2/27/24 14:11, Thiemo Kellner wrote: It is a habit of mine to pad conditions in the where clause. This way, it is easy to comment/uncomment parts of the clause for testing purposes. Coming from Oracle, I missed that using "true" is also possib

Re: Me again with an insert trigger problem

On 2/27/24 14:11, Thiemo Kellner wrote: Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME    from NODE⠒V   where 1 = 1     and ID = new.NODE⠒ID     and 1 = 1; is supposed to be doin

Re: Me again with an insert trigger problem

Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME   from NODE⠒V where 1 = 1    and ID = new.NODE⠒ID    and 1 = 1; is supposed to be doing especially the 1 = 1 tests? The selec

Re: Me again with an insert trigger problem

Thanks. 27.02.2024 19:09:50 Adrian Klaver : > > On 2/27/24 9:49 AM, Thiemo Kellner wrote: >> Hi >> >> I am surprised that my before insert trigger function does not insert any >> rows into NODE_GOOD. >> >> I was under the impression that the trigger

Re: Me again with an insert trigger problem

On 2/27/24 9:49 AM, Thiemo Kellner wrote: Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is

Me again with an insert trigger problem

Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is very simple. Only records of node type "

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On Thu, Feb 22, 2024 at 5:06 PM Erik Wienhold wrote: > On 2024-02-22 22:14 +0100, Vick Khera wrote: > > On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > > > > > For the moment, I think the only feasible solution is for your trigger > > > function to set the

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On 2024-02-22 22:14 +0100, Vick Khera wrote: > On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > > > For the moment, I think the only feasible solution is for your trigger > > function to set the search path it needs by adding a "SET search_path > > = whatever&quo

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > For the moment, I think the only feasible solution is for your trigger > function to set the search path it needs by adding a "SET search_path > = whatever" clause to the function's CREATE command. The error is not in

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

Vick Khera writes: > I've created a database which my vendor (Supabase) cannot > dump/restore/upgrade. Ultimately, it comes down to this trigger statement, > and the fact that the underlying operations needed to perform the `IS > DISTINCT FROM` comparison in the WHEN clause ne

How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

I've created a database which my vendor (Supabase) cannot dump/restore/upgrade. Ultimately, it comes down to this trigger statement, and the fact that the underlying operations needed to perform the `IS DISTINCT FROM` comparison in the WHEN clause need to be found in the `public` schema. D

Re: Trigger to Count Number of Logical Replication Table Changes.

Hi The only thing that can be established is that if the record does not exist, the trigger will not be updating anything in the table "tst.time_audit_tbl" for the condition "table_name = CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME)" Maybe, checking before the UPDATE

Trigger to Count Number of Logical Replication Table Changes.

dated (but it seems less efficient to me). I need the counter to increase after initial sync as well as after regular logical replication sync. Triggers not to work without ENABLE ALWAYS. In addition, If I try trigger that is "FOR EACH STATEMENT" it works only for initial sync and

Re: Detection of which attributes should get set in update trigger

have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be left alone.

Re: Detection of which attributes should get set in update trigger

On Friday, November 10, 2023, Thiemo Kellner wrote: > Hi all > > I have a view, that is a join over 4 tables (the setup of the rule > question). I want to update the central table over the view (by an > instead-of trigger). How can I determine, whether an attribute should ge

Detection of which attributes should get set in update trigger

Hi all I have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be left alone.

Re: Trigger functions and parallelism

On Mon, 02 Oct 2023 13:07:19 -0400 Tom Lane wrote: > "Karl O. Pinc" writes: > > What I'm looking for is some general statements about > > parallel safety for trigger functions. I don't know > > enough about parallelism to even know if it applies >

Re: Trigger functions and parallelism

"Karl O. Pinc" writes: > What I'm looking for is some general statements about > parallel safety for trigger functions. I don't know > enough about parallelism to even know if it applies > to data modification statements. It doesn't, which is

Trigger functions and parallelism

t parallel safety for trigger functions. I don't know enough about parallelism to even know if it applies to data modification statements. It seems to me that, because raising any sort of exception affects transaction state, that triggers which validate data integrity and raise errors m

What changes to a table cannot be detected by a trigger?

Triggers can be set for insert, update, delete, and truncate operations. Alter table operations, like adding or dropping a column from a table, do not cause an update trigger to fire but can be captured by an event trigger. Are there any situations where the data in a table can be changed and the

Re: event trigger clarification

executed by each user action, when invoked in a function > attached > > to a ddl_command_end event trigger." > > When some ddl command is executed within a block, I would like to know > if the > > event trigger fires when the line is executed or at commit time. >

Re: event trigger clarification

ommands returns a list of DDL >> commands >> executed by each user action, when invoked in a function attached to a >> ddl_command_end event trigger." >> When some ddl command is executed within a block, I would like to know if >> the event trigger

Re: event trigger clarification

On Wednesday, August 30, 2023, Marc Millas wrote: > Hi, > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL > commands > executed by each user action, when invoked in a function attached to a > ddl_command_end event trigger." > When some ddl

Re: event trigger clarification

> On 30/08/2023 17:12 CEST Marc Millas wrote: > > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL > commands executed by each user action, when invoked in a function attached > to a ddl_command_end event trigger." > When some ddl command i

event trigger clarification

Hi, the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end event trigger." When some ddl command is executed within a block, I would like to know if the event trigger fire

Re: Dynamically accessing columns from a row type in a trigger

Well, some weeks ago, I read an article about that. The more efficient approach (in pgplsql) is to use hstore. With a similar topic, at the end, I created a group of functions that store in an internal table, data structure for each table under the same generic trigger. I also store, primary key

Re: Dynamically accessing columns from a row type in a trigger

So the less obvious solution that works is to create a temporary table. A little verbose, but I get to keep the types. `CREATE TEMPORARY TABLE _ ON COMMIT DROP AS SELECT OLD.*;` _ as a table name makes things a little easier to type. Rhys Peace & Love | Live Long & Prosper If the connectio

Re: Dynamically accessing columns from a row type in a trigger

nother query. Something along the lines of: > > > > `EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma) > > USING OLD['pk_col']`. > > > > I reckon I may have to look at just generating a trigger function per > > table, or may

Re: Dynamically accessing columns from a row type in a trigger

On Sun, Aug 13, 2023 at 11:27 AM Rhys A.D. Stewart wrote: > > Hey Adrian, > > Thanks for your response. I don't think I explained myself clearly. > pk_col is not the column name. pk_col is a variable that holds the > name of a column. This is one trigger for seve

Re: Dynamically accessing columns from a row type in a trigger

On Sat, Aug 12, 2023 at 1:10 PM Rhys A.D. Stewart wrote: > Am I missing out on a simpler or more elegant solution? > > No, you are not (at least among SQL and pl/pgsql. SQL is strongly and statically typed. Circumventing that has a cost, but at least you do have tools at hand when you find the

Re: Dynamically accessing columns from a row type in a trigger

On 8/12/23 20:21, Rhys A.D. Stewart wrote: Hey Adrian, Thanks for your response. I don't think I explained myself clearly. pk_col is not the column name. pk_col is a variable that holds the name of a column. This is one trigger for several tables with varying structures. So pk_col would

Re: Dynamically accessing columns from a row type in a trigger

Hey Adrian, Thanks for your response. I don't think I explained myself clearly. pk_col is not the column name. pk_col is a variable that holds the name of a column. This is one trigger for several tables with varying structures. So pk_col would be a column specific to the current TG_TABLE

Re: Dynamically accessing columns from a row type in a trigger

On 8/12/23 13:09, Rhys A.D. Stewart wrote: Greetings all, I am writing a trigger and would like to know how to dynamically access a column from the "OLD" variable. pk_col is the column name from the table. I've come up with either doing this: EXECUTE format('SELECT $1

Re: Dynamically accessing columns from a row type in a trigger

On 8/12/23 13:09, Rhys A.D. Stewart wrote: Greetings all, I am writing a trigger and would like to know how to dynamically access a column from the "OLD" variable. pk_col is the column name from the table. I've come up with either doing this: EXECUTE format('SELECT $1

Dynamically accessing columns from a row type in a trigger

Greetings all, I am writing a trigger and would like to know how to dynamically access a column from the "OLD" variable. pk_col is the column name from the table. I've come up with either doing this: EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD; which

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)

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 ex

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 tabl

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

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

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

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 Christo

  1   2   3   4   5   6   7   8   >