[GENERAL] Trigger function problem

Hi, I am trying to migrate data from a master table to her partitions. I am using an update trigger to delete and insert into the master table, and so an insert trigger on the master table redirects the inserted registers to the respective parrtition table. The problem is that the update trigger i

Re: [GENERAL] Trigger function interface

On 1/7/16 6:15 PM, Tatsuo Ishii wrote: On 1/6/16 7:03 PM, Tatsuo Ishii wrote: Is it possible to get the parse tree in a C trigger function which is invoked when DML (INSERT/UPDATE/DELETE against a view) is executed? Yes, it's in fcinfo->flinfo->fn_expr. Thanks for the info. But is this the p

Re: [GENERAL] Trigger function interface

> On 1/6/16 7:03 PM, Tatsuo Ishii wrote: >> Is it possible to get the parse tree in a C trigger function which is >> invoked when DML (INSERT/UPDATE/DELETE against a view) is executed? > > Yes, it's in fcinfo->flinfo->fn_expr. Thanks for the info. But is this the parse tree for the top level quer

Re: [GENERAL] Trigger function interface

On 1/6/16 7:03 PM, Tatsuo Ishii wrote: Is it possible to get the parse tree in a C trigger function which is invoked when DML (INSERT/UPDATE/DELETE against a view) is executed? Yes, it's in fcinfo->flinfo->fn_expr. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analy

[GENERAL] Trigger function interface

Is it possible to get the parse tree in a C trigger function which is invoked when DML (INSERT/UPDATE/DELETE against a view) is executed? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-general ma

Re: [GENERAL] Trigger function, C, lookup of attribute type

On 12/15/15 4:42 AM, Paul wrote: I'm writing a trigger procedure in C to catch updates to a table and write them to a log file. You might find https://github.com/arkhipov/temporal_tables useful. Though, if you just want to log things to a file, you should check out http://pgaudit.org/. -- Ji

Re: [GENERAL] Trigger function, C, lookup of attribute type

I found the solution: SPI_gettype() does the job fine. I was led to that by rummaging through the slony source code to see how they handle the triggers in C. -- Paul Nicholson -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http:/

[GENERAL] Trigger function, C, lookup of attribute type

I'm writing a trigger procedure in C to catch updates to a table and write them to a log file. The function must walk along trigdata->tg_trigtuple pulling out the attributes, comparing them with those in trigdata->tg_newtuple and writing the diffs to a flat ASCII file. I've got a loop over the

Re: [GENERAL] Trigger function cannot reference field name with capital letter

On 14 August 2014 08:10, Patrick Dung wrote: > Thanks all for the help. > > BTW, letter casing is just a preference. > Some people liked to use all small caps, some liked to use all big caps. > I sometimes found that mixed case is more meaningful for the filed (eg. > serialnumber vs serialNumber)

Re: [GENERAL] Trigger function cannot reference field name with capital letter

On 08/13/2014 11:10 PM, Patrick Dung wrote: Thanks all for the help. BTW, letter casing is just a preference. Some people liked to use all small caps, some liked to use all big caps. I sometimes found that mixed case is more meaningful for the filed (eg. serialnumber vs serialNumber) What is yo

Re: [GENERAL] Trigger function cannot reference field name with capital letter

Hi 2014-08-14 8:10 GMT+02:00 Patrick Dung : > Thanks all for the help. > > BTW, letter casing is just a preference. > Some people liked to use all small caps, some liked to use all big caps. > I sometimes found that mixed case is more meaningful for the filed (eg. > serialnumber vs serialNumber)

Re: [GENERAL] Trigger function cannot reference field name with capital letter

Thanks all for the help. BTW, letter casing is just a preference. Some people liked to use all small caps, some liked to use all big caps. I sometimes found that mixed case is more meaningful for the filed (eg. serialnumber vs serialNumber) What is your preference or suggestion? On Thursday,

Re: [GENERAL] Trigger function cannot reference field name with capital letter

On 8/13/2014 9:13 PM, John R Pierce wrote: SELECT * from tbl1 where new."postTimestamp' > timestamp '2014-01-01 00:00:00' oops. SELECT * from tbl1 where new."postTimestamp" > timestamp '2014-01-01 00:00:00' I meant. I should proof what I write, hah! -- john r pierce

Re: [GENERAL] Trigger function cannot reference field name with capital letter

On 8/13/2014 8:52 PM, Patrick Dung wrote: I have a field called postTimestamp. The trigger function could not reference it. When I change my field to post_timestamp. I can reference it from the tigger function. Version is 9.3.5. Any comment? < 2014-08-14 00:23:32.717 HKT >ERROR: post "new" ha

Re: [GENERAL] Trigger function cannot reference field name with capital letter

On 08/13/2014 08:52 PM, Patrick Dung wrote: Hello PGSQL users, I have a field called postTimestamp. The trigger function could not reference it. When I change my field to post_timestamp. I can reference it from the tigger function. Version is 9.3.5. Any comment? < 2014-08-14 00:23:32.717 HKT >E

[GENERAL] Trigger function cannot reference field name with capital letter

Hello PGSQL users, I have a field called postTimestamp. The trigger function could not reference it. When I change my field to post_timestamp. I can reference it from the tigger function. Version is 9.3.5. Any comment? < 2014-08-14 00:23:32.717 HKT >ERROR:  post "new" has no field "posttimestam

[GENERAL] Trigger function permissions

Just want to make sure I'm understanding the permissions needed for trigger functions as well as making sure this is what the developers intended before I go assuming things will always work this way. Also as a sanity check for myself that I'm not missing something obvious. I have an extension (ht

Re: [GENERAL] Trigger function on Warm Standby

ning chan writes: > Hi all, > > I have a Primary Standby setup with streaming replication. > Trigger is created on a table, and all it does it to log a message. > > The trigger works as expected on Primary, however, I don't see the same on > standby. > > I alter the table to have ENABLE ALWAYS T

[GENERAL] Trigger function on Warm Standby

Hi all, I have a Primary Standby setup with streaming replication. Trigger is created on a table, and all it does it to log a message. The trigger works as expected on Primary, however, I don't see the same on standby. I alter the table to have ENABLE ALWAYS TRIGGER, I verified the setting on bo

Re: [GENERAL] Trigger Function return values

On Tuesday, March 22, 2011 3:12:56 pm Andy Chambers wrote: > Hi, > > How is the return value of a trigger function defined in plpgsql used? I > can't find > anything in the documentation but some of the examples return NULL, and > others return > something like NEW. > > It seems that if the retu

Re: [GENERAL] Trigger Function return values

On Mar 22, 2011, at 4:12 PM, Andy Chambers wrote: > How is the return value of a trigger function defined in plpgsql used? I > can't find > anything in the documentation but some of the examples return NULL, and > others return > something like NEW.

[GENERAL] Trigger Function return values

Hi, How is the return value of a trigger function defined in plpgsql used? I can't find anything in the documentation but some of the examples return NULL, and others return something like NEW. It seems that if the return statement is omitted, an error occurs when the trigger is fired.

Re: [GENERAL] Trigger function language

Vincenzo Romano writes: > Is there any performance difference in writing trigger function in SQL > rather than PL/PgSQL? SQL functions don't support being called as triggers, so performance questions are a bit beside the point ... regards, tom lane -- Sent via pgsql-gen

[GENERAL] Trigger function language

Hi all. Is there any performance difference in writing trigger function in SQL rather than PL/PgSQL? In my case the body would actually be 1 line. -- Vincenzo Romano NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your s

Re: [GENERAL] Trigger Function and backup

hello Arndt, Thanks for the input. I was successfully able to implement "rubyrep", on a windows system (I used windows 7). Is there any way i can implement Master Slave replication by rubyrep. Regards Nishkarsh Arndt Lehmann-2 wrote: > > Hi Merlin, > > you could also consider giving "rubyre

Re: [GENERAL] Trigger Function and backup

On Jun 23, 10:20 am, laconi...@gmail.com (Andrew Smith) wrote: > Arndt, > > Your website says rubyrep runs on Linux and Windows - am I going to have > difficulties if I want to try it on Solaris 10? > > Andrew > > 2009/6/23 Arndt Lehmann > > > On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishka

Re: [GENERAL] Trigger Function and backup

Arndt, Your website says rubyrep runs on Linux and Windows - am I going to have difficulties if I want to try it on Solaris 10? Andrew 2009/6/23 Arndt Lehmann > On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishkarsh) wrote: > > Hi Merlin, thanks for the detailed input. > > > > As per ur sug

Re: [GENERAL] Trigger Function and backup

On Jun 16, 7:48 pm, nishkars...@rediffmail.com (Nishkarsh) wrote: > Hi Merlin, thanks for the detailed input. > > As per ur suggestion i will try to implement Slony-I. > > I think i will need some help to do it. > > I am useing Postgres 8.3.7, on Windows. > > I was following the Slony-I example in

Re: [GENERAL] Trigger Function and backup

On Tue, Jun 16, 2009 at 6:48 AM, Nishkarsh wrote: > > Hi Merlin, thanks for the detailed input. > > As per ur suggestion i will try to implement Slony-I. > > I think i will need some help to do it. > > I am useing Postgres 8.3.7, on Windows. > > I was following the Slony-I example in the help for p

Re: [GENERAL] Trigger Function and backup

Hi Merlin, thanks for the detailed input. As per ur suggestion i will try to implement Slony-I. I think i will need some help to do it. I am useing Postgres 8.3.7, on Windows. I was following the Slony-I example in the help for pgAdmin III. I am able to perform the steps from 1-7. Step 8 : cre

Re: [GENERAL] Trigger Function and backup

On Mon, Jun 15, 2009 at 4:29 AM, Nishkarsh wrote: > Hello every one, > > I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few > days) on windows platform. > > I had tried using Slony-I for replication and was not able to create a > cluster. > > After struggling for some time i

Re: [GENERAL] Trigger Function and backup

On Mon, Jun 15, 2009 at 5:50 AM, Havasvölgyi Ottó wrote: > Hi, > > I have found the following strangeness on Windows: > > create table round_test (id int primary key, value double precision); > insert into round_test(id, value) values(1, 1.5); > insert into round_test(id, value) values(2, -1.5); >

Re: [GENERAL] Trigger Function and backup

Hi, I have found the following strangeness on Windows: create table round_test (id int primary key, value double precision); insert into round_test(id, value) values(1, 1.5); insert into round_test(id, value) values(2, -1.5); insert into round_test(id, value) values(3, 3.5); select round(value) f

[GENERAL] Trigger Function and backup

Hello every one, I am new to databases. I am using Postgres 8.2 (Migrating to 8.3.7 in few days) on windows platform. I had tried using Slony-I for replication and was not able to create a cluster. After struggling for some time i decide to implement a way around to take differential backup. A

Re: [GENERAL] Trigger function cost

> From: Tom Lane > > > Is there any reason to mess with this? > > No. The planner doesn't actually bother to figure the > cost of triggers > anyway, since presumably every correct plan will fire the > same set of > triggers. So even if you had a more accurate cost estimate > than that > one,

Re: [GENERAL] Trigger function cost

Glyn Astill writes: > I see that when I create a volatile plpgsql trigger function it gets given a > cost of 100 and a c function gets given a cost of 1. > Is there any reason to mess with this? No. The planner doesn't actually bother to figure the cost of triggers anyway, since presumably eve

[GENERAL] Trigger function cost

Hi Chaps, Can anyone point me to docs for trigger function estimated cost? I see that when I create a volatile plpgsql trigger function it gets given a cost of 100 and a c function gets given a cost of 1. Is there any reason to mess with this? Thanks Glyn -- Sent via pgsql-general mailin

Re: [GENERAL] Trigger function is not called

Bill wrote: The thing that has me confused is that the following table, trigger and trigger function work perfectly and the primary key for this table is also bigint not null. I added a bigint not null domain to this schema and changed the data type of the key to the domain and then I get the

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: The thing that has me confused is that the following table, trigger and trigger function work perfectly and the primary key for this table is also bigint not null. Actually, after looking closer, I think the problem with your previous e

Re: [GENERAL] Trigger function is not called

Klint Gore <[EMAIL PROTECTED]> writes: > ... With the not null definition in the domain, this > blows up before anything else has a chance. Right. Forming the proposed row-to-insert involves coercing the data to the correct data types, and for domain types enforcing the domain constraints is se

Re: [GENERAL] Trigger function is not called

Bill <[EMAIL PROTECTED]> writes: > The thing that has me confused is that the following table, trigger and > trigger function work perfectly and the primary key for this table is > also bigint not null. Actually, after looking closer, I think the problem with your previous example is that you cr

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: I removed the domain from the category_id and version columns leaving the following table, trigger function and trigger. The trigger function is still not called when I insert a new row. Any other ideas? You're still expecting the trigg

Re: [GENERAL] Trigger function is not called

Bill <[EMAIL PROTECTED]> writes: > I removed the domain from the category_id and version columns leaving > the following table, trigger function and trigger. The trigger function > is still not called when I insert a new row. Any other ideas? You're still expecting the trigger to get invoked bef

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: Is it possible to create a type and use that instead of the domain or will I have the same problem with a type? You'd have the same problem. By the time the trigger sees it, the row has already been converted to the table's column datat

Re: [GENERAL] Trigger function is not called

You'd have the same problem. By the time the trigger sees it, the row has already been converted to the table's column datatype(s), so any exception associated with a datatype or domain would be thrown already. A lot of people seem to have trouble with this concept; I dunno what data representat

Re: [GENERAL] Trigger function is not called

Bill <[EMAIL PROTECTED]> writes: > Is it possible to create a type and use that instead of the domain or > will I have the same problem with a type? You'd have the same problem. By the time the trigger sees it, the row has already been converted to the table's column datatype(s), so any exceptio

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: In a newsgroup posting someone suggested that constraint checks on domains occur before the before insert trigger. Yeah, that is the case, but if a domain check was failing then the row wouldn't get inserted, so I'm not clear on how this

Re: [GENERAL] Trigger function is not called

Bill <[EMAIL PROTECTED]> writes: > In a newsgroup posting someone suggested that constraint checks on > domains occur before the before insert trigger. Yeah, that is the case, but if a domain check was failing then the row wouldn't get inserted, so I'm not clear on how this matches up with your r

Re: [GENERAL] Trigger function is not called

Tom Lane wrote: Bill <[EMAIL PROTECTED]> writes: PostgreSQL 8.3 on Windows. I have the table below which has a before insert trigger. The CREATE TRIGGER statement and the trigger function are also shown below. The script you show attempts to create the trigger before creating the func

Re: [GENERAL] Trigger function is not called

Bill <[EMAIL PROTECTED]> writes: > PostgreSQL 8.3 on Windows. I have the table below which has a before > insert trigger. The CREATE TRIGGER statement and the trigger function > are also shown below. The script you show attempts to create the trigger before creating the function, which of course

[GENERAL] Trigger function is not called

PostgreSQL 8.3 on Windows. I have the table below which has a before insert trigger. The CREATE TRIGGER statement and the trigger function are also shown below. When I insert a row into this table using pgAdmin III and the INSERT statement insert into note.category (category_id, category) valu

Re: [GENERAL] Trigger function that works with both updates and deletes?

Perfect, thanks Michael Fuhr wrote: > > On Mon, Jun 18, 2007 at 06:07:37PM -0700, novnov wrote: >> First, when a record is being deleted, OLD refers to the rec just deleted >> (or about to be deleted)? > > Correct. > >> Second, while I could write two trigger functions, one dealing with >> ad

Re: [GENERAL] Trigger function that works with both updates and deletes?

> Second, while I could write two trigger functions, one dealing with > add/update, the other with deletes, it's probably neater to > have a single > trigger function and have it discriminate "am I being called > for a delete, > or an add/update?" I don't know how to determine the type > record

Re: [GENERAL] Trigger function that works with both updates and deletes?

On Mon, Jun 18, 2007 at 06:07:37PM -0700, novnov wrote: > First, when a record is being deleted, OLD refers to the rec just deleted > (or about to be deleted)? Correct. > Second, while I could write two trigger functions, one dealing with > add/update, the other with deletes, it's probably neater

[GENERAL] Trigger function that works with both updates and deletes?

Most of the trigger fuctions I've written work on new and updated records referencing NEW. etc. I will need some of the trigger functions to work with record deletions too. First, when a record is being deleted, OLD refers to the rec just deleted (or about to be deleted)? Second, while I could

Re: [GENERAL] Trigger function which inserts into table; values from lookup

Yes, I think that would work and mabye I'll use that approach. But is there no way to implement as I orginally intended? Also, am I right in thinking that this approach is more efficient than a looping operation? William Leite Araújo wrote: > > Maybe you can use a "LEFT OUTER JOIN" ... >

Re: [GENERAL] Trigger function which inserts into table; values from lookup

Maybe you can use a "LEFT OUTER JOIN" ... CREATE or REPLACE FUNCTION "public"."tproc_handle_item_active"() RETURNS "pg_catalog"."trigger" AS $BODY$ DECLARE rec_item record; int_org_id integer; BEGIN -- whenever an item is set active; create entries in the following table:

Re: [GENERAL] Trigger function which inserts into table; values from lookup

No and update would not be needed; but the capability would be close enough, I'd just skip the update, do nothing for that record. But from the sound of it, the example you're suggesting involves a loop or something of that order. I could have written this using a loop but thought a bulk operatio

Re: [GENERAL] Trigger function which inserts into table; values from lookup

On 21/05/2007 05:26, novnov wrote: OK, but, how do I set this up to do what I need? I want an insert that would create a dupe key to be rolled back, and inserts that would not create dupe keys to be committed. Do you specifically need it in a trigger? I seem to recall an example in the docs

Re: [GENERAL] Trigger function which inserts into table; values from lookup

OK, but, how do I set this up to do what I need? I want an insert that would create a dupe key to be rolled back, and inserts that would not create dupe keys to be committed. Tom Lane-2 wrote: > > novnov <[EMAIL PROTECTED]> writes: >> Any clue re my question? > > You've placed the INSERT ins

Re: [GENERAL] Trigger function which inserts into table; values from lookup

novnov <[EMAIL PROTECTED]> writes: > Any clue re my question? You've placed the INSERT inside the BEGIN/EXCEPTION block, ergo it's part of the work to be rolled back on exception. regards, tom lane ---(end of broadcast)--- T

Re: [GENERAL] Trigger function which inserts into table; values from lookup

It's an after trigger. Any clue re my question? Alvaro Herrera-7 wrote: > > novnov escribió: >> >> Inching closer; the following handles the dupe key error but doesn't >> insert >> the rows it should either. So, the exception is ending the insert, and >> not >> continuing to insert for rows t

Re: [GENERAL] Trigger function which inserts into table; values from lookup

novnov escribió: > > Inching closer; the following handles the dupe key error but doesn't insert > the rows it should either. So, the exception is ending the insert, and not > continuing to insert for rows that don't violate the unique key restraint. > Is there a way around this or will I need to

Re: [GENERAL] Trigger function which inserts into table; values from lookup

Inching closer; the following handles the dupe key error but doesn't insert the rows it should either. So, the exception is ending the insert, and not continuing to insert for rows that don't violate the unique key restraint. Is there a way around this or will I need to take a different approach?

Re: [GENERAL] Trigger function which inserts into table; values from lookup

So, I may have hammered out the basic trigger function. The error trapping part is a complete mystery to me. I'll post the trigger function below in the hopes that someone will at least comment on the error handling part. The error, as expected is 'duplicate key violates unique contraint blah bla

[GENERAL] Trigger function which inserts into table; values from lookup

Not sure it the title of this post gives a clear message...I need to have a trigger function insert records into a table under certain conditions, where the values inserted are partially based on the results of a select query. table t_item item_id (pk) item_name item_org_id item_active table

Re: [GENERAL] Trigger function to audit any kind of table

On Tue, Jun 06, 2006 at 01:54:01PM -0500, Sergio Duran wrote: > Hello, > > I would like to know if it is possible to create a trigger function which > does something like > > CREATE OR REPLACE FUNCTION table_audit() RETURNS TRIGGER AS $table_audit$ > BEGIN > INSERT INTO audit SELECT TG_OP, curre

[GENERAL] Trigger function to audit any kind of table

Hello,I would like to know if it is possible to create a trigger function which does something likeCREATE OR REPLACE FUNCTION table_audit() RETURNS TRIGGER AS $table_audit$BEGIN  INSERT INTO audit SELECT TG_OP, current_timestampmp, current_user, OLD, NEW;   RETURN NEW;END $table_audit$ LANGUAGE plp

[GENERAL] Trigger & Function

Title: Trigger & Function I'm trying to create a trigger (AFTER INSERT, UPDATE, DELETE) as an audit routine inserting into an audit table the "before" and "after" views of the row being acted upon.  My problem is I defined the "before" and "after" fields in the audit table as TEXT and when I t

[GENERAL] trigger function SQL statement

If I have the following table: create table my_table( start TIME, end TIME ); and then I wanted a function as a trigger that would compare any INSERTS or UPDATES to see if there was any overlap in the interval between start and end of the new rows and existing rows, is there a single state

Re: [GENERAL] Trigger/Function problem

That was it! Thanks Tom. I just put this functionality into phpPgAdmin and of course it is taking the newline char from the browser's OS. Thanks for all your help! -Dan > "Dan Wilson" <[EMAIL PROTECTED]> writes: > > I get the following error: > > NOTICE: plpgsql: ERROR during compile of f_

Re: [GENERAL] Trigger/Function problem

"Dan Wilson" <[EMAIL PROTECTED]> writes: > I get the following error: > NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1 > "RROR: parse error at or near " Just like that, eh? It looks like the parser is spitting up on a \r in the function text. Try saving your script with Unix

Re: [GENERAL] Trigger/Function problem

If you look at my function definition, you can see that this is not within the function body. This is the testing of the trigger which produces the error. It's just a plain old SQL statment that initiates the trigger. -Dan - Original Message - From: "Robert B. Easter" <[EMAIL PROTECTED

Re: [GENERAL] Trigger/Function problem

On Thursday 14 December 2000 21:10, Robert B. Easter wrote: > On Thursday 14 December 2000 21:27, Dan Wilson wrote: > > I'm totally fine up to this point... then I try this: > > > > UPDATE help SET site_id = 'APW' WHERE help_id = 2; > > > > I get the following error: > > > > NOTICE: plpgsql: ERRO

Re: [GENERAL] Trigger/Function problem

On Thursday 14 December 2000 21:27, Dan Wilson wrote: > > I'm totally fine up to this point... then I try this: > > UPDATE help SET site_id = 'APW' WHERE help_id = 2; > > I get the following error: > > NOTICE: plpgsql: ERROR during compile of f_auto_date near line 1 > "RROR: parse error at or ne

[GENERAL] Trigger/Function problem

I'm having problems with a trigger/function and I think it's actually a system problem but I have no clue how to fix it. The trigger is supposed to automatically timestamp the record when it is altered. I've never used anything more than a sql function before so the plpgsql is new to me. Here's