Re: [GENERAL] Trigger problems/questions

On Thu, Jun 15, 2017 at 1:56 PM, Tom Lane wrote: > Jim Fulton writes: > > I have an object database that's mirrored to a table with data in a JSONB > > column. Data are organized into "communities". Community ids aren't > > stored directly in content but can be found by recursively following >

Re: [GENERAL] Trigger problems/questions

Jim Fulton writes: > I have an object database that's mirrored to a table with data in a JSONB > column. Data are organized into "communities". Community ids aren't > stored directly in content but can be found by recursively following > __parent__ properties. I want to be able to index content

Re: [GENERAL] Trigger problems/questions

On Thu, Jun 15, 2017 at 1:44 PM, Adrian Klaver wrote: > On 06/15/2017 10:23 AM, Jim Fulton wrote: > ... > I do not pretend to fully understand what the the triggers/functions are > really doing, but I did notice this: > > create or replace function populate_community_zoid_triggerf() > > ... >

Re: [GENERAL] Trigger problems/questions

On 06/15/2017 10:23 AM, Jim Fulton wrote: I have an object database that's mirrored to a table with data in a JSONB column. Data are organized into "communities". Community ids aren't stored directly in content but can be found by recursively following __parent__ properties. I want to be able

Re: [GENERAL] Trigger based logging alternative to table_log

> From: Jeff Janes > To: "pgsql-general@postgresql.org" > Sent: Monday, 27 March 2017, 18:08 > Subject: [GENERAL] Trigger based logging alternative to table_log > > I have some code which uses table_log > (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to > selected tables.

Re: [GENERAL] Trigger based logging alternative to table_log

Jeff Janes schrieb am 27.03.2017 um 19:07: I have some code which uses table_log (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to selected tables. I don't use the restore part, just the logging part. It creates a new table for each table being logged, with several additiona

Re: [GENERAL] Trigger based logging alternative to table_log

> I have some code which uses table_log > (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to > selected tables.  > I don't use the restore part, just the logging part.   > It creates a new table for each table being logged, with several additional > columns, and adds triggers

Re: [GENERAL] Trigger is not working for Inserts from the application

Hi Jim, The issue is now resolved thanks to Tom and Adrian. regards Kiran On Sun, Sep 11, 2016 at 4:16 AM, Jim Nasby wrote: > On 9/10/16 6:03 AM, Kiran wrote: > >> If I insert a record from my application using following code : >> db.myschema.cf_question.insert({ >> cf_question_type_id:request

Re: [GENERAL] Trigger is not working for Inserts from the application

On 9/10/16 6:03 AM, Kiran wrote: If I insert a record from my application using following code : db.myschema.cf_question.insert({ cf_question_type_id:request.payload.type_id, cf_question_category_id:request.payload.cat_id, lang:request.payload.lang, body:request.payload.body } The above app code

Re: [GENERAL] Trigger is not working for Inserts from the application

Dear Tom, Adrian, Excellent catch! Thank you very very much to both. It is resolved now. I can sleep now. Good night from Sweden. regards Kiran On Sat, Sep 10, 2016 at 11:18 PM, Adrian Klaver wrote: > On 09/10/2016 02:09 PM, Kiran wrote: > >> Hi Adrian, >> >> The JSON value is inserted into a

Re: [GENERAL] Trigger is not working for Inserts from the application

On 09/10/2016 02:09 PM, Kiran wrote: Hi Adrian, The JSON value is inserted into a column in the database which I can see. But the trigger which has to convert this JSON value in not tsvector column is not updating that column. As Tom explained, in your trigger function you have: to_tsvector('

Re: [GENERAL] Trigger is not working for Inserts from the application

Hi Adrian, The JSON value is inserted into a column in the database which I can see. But the trigger which has to convert this JSON value in not tsvector column is not updating that column. regards On Sat, Sep 10, 2016 at 10:27 PM, Tom Lane wrote: > Kiran writes: > > LOG: execute : INSERT IN

Re: [GENERAL] Trigger is not working for Inserts from the application

On 09/10/2016 02:02 PM, Kiran wrote: Hi Tom, I have checked and the trigger is not disabled. and \drds results Role=blank database = mydatabase settings = default_text_search_config=pg_catalog.swedish Any other tips or suggestions please. Did you see this post?: https://www.postgresql.org/m

Re: [GENERAL] Trigger is not working for Inserts from the application

Hi Tom, I have checked and the trigger is not disabled. and \drds results Role=blank database = mydatabase settings = default_text_search_config=pg_catalog.swedish Any other tips or suggestions please. regards Kiran On Sat, Sep 10, 2016 at 9:26 PM, Tom Lane wrote: > Kiran writes: > > But

Re: [GENERAL] Trigger is not working for Inserts from the application

Kiran writes: > LOG: execute : INSERT INTO "myschema"."cf_question" > ("cf_question_type_id", "cf_question_category_id", "lang", "body") VALUES > ($1, $2, $3, $4) RETURNING * > DETAIL: parameters: $1 = '1', $2 = '9', $3 = 'swedish', $4 = '{"name": "Do > you like Pizza ?", "type": "cat", "store":

Re: [GENERAL] Trigger is not working for Inserts from the application

Hi Adrian, This is the exact log in the file as it appears: DETAIL: parameters: $1 = '', $2 = '' LOG: connection received: host=localhost port=53284 LOG: connection authorized: user=deva database=mydatabase LOG: connection received: host=localhost port=53285 LOG: connection authorized: user=

Re: [GENERAL] Trigger is not working for Inserts from the application

Kiran writes: > But I do not know how to check whether the application is subject to > different ALTER USER SET parameters. psql's \drds ("display role/database SETs") would help. BTW, have you checked that the trigger is not disabled, and that there isn't another trigger undoing its work?

Re: [GENERAL] Trigger is not working for Inserts from the application

On 09/10/2016 11:39 AM, Kiran wrote: Hi Adrian, Thanks for your response. I tried with logging. The application is inserting the record that I am passing into the database. But the trigger is not firing. What is the text of the complete statement as it appears in the logs? When you do the INS

Re: [GENERAL] Trigger is not working for Inserts from the application

Dear Tom, Thanks for your detailed reply. (1) Application is connecting to the same database. (2) Application is inserting to the same table without much luck with the trigger. (3) Application is issuing the right insert command. (4) I am not able to check this, is there any way I can check this?

Re: [GENERAL] Trigger is not working for Inserts from the application

Hi Adrian, Thanks for your response. I tried with logging. The application is inserting the record that I am passing into the database. But the trigger is not firing. I have been looking into this issue since morning with out any positive outcome :(. If you have any other tips, it will be really h

Re: [GENERAL] Trigger is not working for Inserts from the application

Adrian Klaver writes: > On 09/10/2016 03:59 AM, Kiran wrote: >> What could be the problem ? Why trigger is not working if I insert from the >> application ? Am I doing anything wrong ? > If you have not, turn on log_statement: > https://www.postgresql.org/docs/9.5/static/runtime-config-logging.ht

Re: [GENERAL] Trigger is not working for Inserts from the application

On 09/10/2016 03:59 AM, Kiran wrote: Hi, *Problem background :* I have a *function in the DB* as follows CREATE FUNCTION question_tsv_trigger() RETURNS trigger AS $BODY$ begin New.weighted_tsv := to_tsvector('swedish',coalesce(New.body->>'qtext','')::text); RAISE NOTICE 'TRIGER called on %', TG_

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

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:/

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

On 03/06/15 08:40, Andreas Ulbrich wrote: On 02.06.2015 22:12, Melvin Davidson wrote: Your problem is in your design. If you do it like this: CREATE TABLE A ( p_col serial PRIMARY KEY, acol integer ); CREATE TABLE B() INHERITS (A); INSERT INTO A(acol) VALUES (1); INSERT INTO B(acol) VALUES

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

On 02.06.2015 22:12, Melvin Davidson wrote: Your problem is in your design. If you do it like this: CREATE TABLE A ( p_col serial PRIMARY KEY, acol integer ); CREATE TABLE B() INHERITS (A); INSERT INTO A(acol) VALUES (1); INSERT INTO B(acol) VALUES (2); SELECT * FROM A; SELECT * FROM B; Th

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

Your problem is in your design. If you do it like this: CREATE TABLE A ( p_col serial PRIMARY KEY, acol integer ); CREATE TABLE B() INHERITS (A); INSERT INTO A(acol) VALUES (1); INSERT INTO B(acol) VALUES (2); SELECT * FROM A; SELECT * FROM B; Then the sequence (p_col) will be UNIQUE across

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

On 02.06.2015 16:20, Melvin Davidson wrote: You can use the following to list the triggers and see what functions they call. Then you can check pg_proc to see how TRUNCATE is used in prosrc. SELECT c.relname, t.tgname, p.pronameAS function_called, t.tgconstraint

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

You can use the following to list the triggers and see what functions they call. Then you can check pg_proc to see how TRUNCATE is used in prosrc. SELECT c.relname, t.tgname, p.pronameAS function_called, t.tgconstraint AS is_constraint, CASE WHEN t.tgconstrre

Re: [GENERAL] TRIGGER TRUNCATE -- CASCADE or RESTRICT

Andreas Ulbrich wrote: > I'm in a handle for a trigger for TRUNCATE. Is it possible to find out > whether the TRUNCATE TABLE ist called with CASCADE? I don't think there is. But you can find out the table where the trigger is defined and examine if any foreign key constraints are referring to it.

Re: [GENERAL] trigger Before or After

On 11/10/2014 10:38 PM, avpro avpro wrote: hi, in the pgsql documentation (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) i haven't seen anything referring to: how is affected the data inserted in the new table by a trigger Before Insert compared with a trigger After Insert?

Re: [GENERAL] trigger Before or After

avpro avpro wrote: > in the pgsql documentation > (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html) > > > i haven't seen anything referring to: how is affected the data inserted in > the new table by a trigger > Before Insert compared with a trigger After Insert? and anything re

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

Re: [GENERAL] Trigger to convert UNIX time to timestamp without time zone.

Andrew Sullivan wrote: > Are you sure you want this without time zone?  In my experience, > almost every time people think they want "without time zone" they > actually don't. +1 Basically, if you want to capture a moment in time, such as when some event happened or some measurement was taken,

Re: [GENERAL] Trigger to convert UNIX time to timestamp without time zone.

On Fri, Jun 06, 2014 at 02:19:50PM +0100, Alberto Olivares wrote: > How can I create a trigger to transform the time from UNIX to timestamp > without time zone every time a new record is inserted into my database? This is in the manual, section 9.8: to_timestamp(double precision) It's always a li

Re: [GENERAL] Trigger to convert UNIX time to timestamp without time zone.

On 06/06/2014 06:19 AM, Alberto Olivares wrote: Hello, I want to insert data into a column "timestamp without time zone" data type. The problem is I am receiving the data in UNIX time. How can I create a trigger to transform the time from UNIX to timestamp without time zone every time a new rec

Re: [GENERAL] Trigger Firing Order

That sounds like a nice suggestion. I guess it could get rid of most of the issues I forsee. I should follow that for my cases too. I guess we can live without an ORDER clause. Thanks everyone for helping.

Re: [GENERAL] Trigger Firing Order

Sameer Kumar wrote: > If I have a trigger which add primary key to my inserted row > ("before trigger"). Now if I plan to create new set of triggers > for AUDITING or replication (where either I have no flexibility > of choosing a name or the trigger name has to follow a standard), > then I need

Re: [GENERAL] Trigger Firing Order

> > But with certain packaged products who create their own triggers, I won't >> have control over this. > I don't have a lot of sympathy for that argument. If the product is > capable of creating Postgres-compatible triggers at all, it should be > aware that the name is a significant property,

Re: [GENERAL] Trigger Firing Order

Sameer Kumar writes: > Is it possible for me to define the order in which triggers will be fired? Sure: choose their names so that their alphabetical ordering is the firing order you want. But I see you knew that. > But with certain packaged products who create their own triggers, I won't > hav

Re: [GENERAL] Trigger Firing Order

Sameer Kumar wrote > Hi, > > Is it possible for me to define the order in which triggers will be fired? > > So far what I have understood from PostgreSQL documentation, in order to > fire 2 triggers in sequence (say trigger1 and trigger2) on a table, I need > to name them in that way. > But with

Re: [GENERAL] trigger without trigger call

On Mon, Oct 14, 2013 at 6:02 AM, Philipp Kraus < philipp.kr...@tu-clausthal.de> wrote: > > Am 14.10.2013 um 11:49 schrieb Alban Hertroys : > > > On Oct 14, 2013, at 8:18, Philipp Kraus > wrote: > > > >> Hello, > >> > >> I have written a update & delete trigger of a table. > >> My delete trigger r

Re: [GENERAL] trigger without trigger call

Am 14.10.2013 um 11:49 schrieb Alban Hertroys : > On Oct 14, 2013, at 8:18, Philipp Kraus wrote: > >> Hello, >> >> I have written a update & delete trigger of a table. >> My delete trigger runs an update statement but this create a (semantic) >> problem. >> How can I disable the update trigge

Re: [GENERAL] trigger without trigger call

On Oct 14, 2013, at 8:18, Philipp Kraus wrote: > Hello, > > I have written a update & delete trigger of a table. > My delete trigger runs an update statement but this create a (semantic) > problem. > How can I disable the update trigger for only this update call within the > delete trigger? >

Re: [GENERAL] trigger without trigger call

Hello you can disable trigger by ALTER TABLE tablename DISABLE TRIGGER triggername; but it should be executed with owner rights on table. This statement works perfect, but a fact, so you need it signalize so you do some wrong. Triggers should to be used primary for checking, logging, calculating

Re: [GENERAL] Trigger to update table info

Hi, In case you are not keen on getting the latest and really accurate counts/size, you could just refer the views readily available - http://stackoverflow.com/questions/2596670/how-do-you-find-the-row-count-for-all-your-tables-in-postgres You won't get the updatetime, though. Regards, Jayadevan

Re: [GENERAL] Trigger to update table info

On Mon, Aug 12, 2013 at 12:46 PM, Arvind Singh wrote: > Hello friends, > > I have a table with the following structure > > > Create table postablestatus > > ( > > tablename varchar(30) NOT NULL, > > updatetime timestamp, > > reccount int, > >

Re: [GENERAL] Trigger and deadlock

Loïc Rollus wrote: > It's ok. > Before the insert, The foreign key constraint locked the row. If transaction > A and B lock the row with > FK, before doing UPDATE, they were stuck. > I found a solution by creating an "before insert" trigger with a simple > SELECT FROM UPDATE on the row. You mean

Re: [GENERAL] Trigger and deadlock

Hi, It's ok. Before the insert, The foreign key constraint locked the row. If transaction A and B lock the row with FK, before doing UPDATE, they were stuck. I found a solution by creating an "before insert" trigger with a simple SELECT FROM UPDATE on the row. Loïc 2013/7/29 Loïc Rollus > He

Re: [GENERAL] Trigger and deadlock

Here is pg_lock for relation Y (= 2027300) locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted --+--+--+--+---++---+-+---+--

Re: [GENERAL] Trigger and deadlock

Hi, Thanks for your quick reply! I found the table. But the ctid of the row has changed. But during my test, I update only 1 row from this table, so I know the row. I had already put log_statement to 'all'. It's strange because in the log, I only see simple "SELECT ... FROM" on this table (no UPD

Re: [GENERAL] Trigger and deadlock

Loïc Rollus wrote: > I've try to make some concurrency robustness test with an web server app that > use Hibernate and > Postgres. > It seems that my trigger make deadlock when multiple thread use it. > > I will try to simplify examples: > I have a table "films"(id, title,director) and a table >

Re: [GENERAL] TRIGGER EVENT

Sajeev Mayandi wrote: > Hi, > > I am process of porting from sybase to postgresql. Is there syntax equivalent > of the TRIGGER EVENT Statement (Please see the sybase help for trigger event > below) in Postgresql. NOTE the TRIGGER EVENT in sybase could be used to > trigger an user defined event.

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

Re: [GENERAL] Trigger of Transaction

On Mon, Apr 1, 2013 at 8:41 PM, Juan Pablo Cook wrote: > Hi everyone! I need your help with this problem. > > I'm using PostgreSQL *9.2 Server* & the latest jdbc > driver: postgresql-9.2-1002.jdbc4.jar > > I have a many to one relation. I have this piece of code: > > con.setAutoCommit(false); //t

Re: [GENERAL] Trigger / constraint issue

On 12/06/2012 10:31 AM, Glenn Pierce wrote: OK I have got it down to a simple test #connect_string = 'dbname=bmos user=bmos' connect_string = 'dbname=bmos user=postgres' if __name__ == "__main__": conn = psycopg2.connect(connect_string) cur = conn.cursor(cursor_factory=psycopg2.extras

Re: [GENERAL] Trigger / constraint issue

OK I have got it down to a simple test #connect_string = 'dbname=bmos user=bmos' connect_string = 'dbname=bmos user=postgres' if __name__ == "__main__": conn = psycopg2.connect(connect_string) cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cur.execute("INSERT INTO senso

Re: [GENERAL] Trigger / constraint issue

On 12/05/2012 02:24 PM, Glenn Pierce wrote: The error I get is new row for relation "sensor_values_2011q3" violates check constraint "sensor_values_2011q3_timestamp_check"CONTEXT: SQL statement "INSERT INTO sensor_values_2011q3 VALUES (NEW.*)"PL/pgSQL function "sensor_values_timestamp_sensor_

Re: [GENERAL] trigger and plpgsq help needed

, 2012 2:04:46 PM Subject: Re: [GENERAL] trigger and plpgsq help needed RobR, I have tried it 'IF FOUND' but not getting the desired result Thanks, Sunday Olutayo - Original Message - From: "Rob Richardson" To: pgsql-general@postgresql.org Sent: Wednesday, Se

Re: [GENERAL] trigger and plpgsq help needed

sadeeb.com - Original Message - From: "Chris Ernst" To: pgsql-general@postgresql.org Sent: Wednesday, September 26, 2012 7:10:03 PM Subject: Re: [GENERAL] trigger and plpgsq help needed On 09/26/2012 10:42 AM, SUNDAY A. OLUTAYO wrote: > This IF last_id IS NULL THEN suppos

Re: [GENERAL] trigger and plpgsq help needed

RobR, I have tried it 'IF FOUND' but not getting the desired result Thanks, Sunday Olutayo - Original Message - From: "Rob Richardson" To: pgsql-general@postgresql.org Sent: Wednesday, September 26, 2012 7:14:46 PM Subject: Re: [GENERAL] trigger an

Re: [GENERAL] trigger and plpgsq help needed

On 09/26/2012 09:42 AM, SUNDAY A. OLUTAYO wrote: Dear all, I have issue with the red portion function below; This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is the select query did not found it then execute the rest of the red sql but it always fail to insert the *NEW.amou

Re: [GENERAL] trigger and plpgsq help needed

On 09/26/2012 10:42 AM, SUNDAY A. OLUTAYO wrote: > This IF last_id IS NULL THEN suppose to test if last_id is NULL, that is > the select query did not found it > then execute the rest of the red sql but it always fail to insert the > *NEW.amount *into*amount*, every other things fine. I believe wh

Re: [GENERAL] trigger and plpgsq help needed

Why not use IF FOUND? RobR From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of SUNDAY A. OLUTAYO Sent: Wednesday, September 26, 2012 12:43 PM To: pgsql-general@postgresql.org Subject: [GENERAL] trigger and plpgsq help needed Dear all, I have issue

Re: [GENERAL] trigger on view returning created serial

On Tue, 2012-06-12 at 22:35 +0200, Philipp Kraus wrote: > Hello, > > I have created a view and on this view a trigger, which is called on an > insert command on the view. > Within the trigger I run an insert on a table and one of the table fields > uses a serial sequence, that > creates values.

Re: [GENERAL] trigger when clause

On Tue, Apr 10, 2012 at 5:10 PM, Jeff Davis wrote: > On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote: > > > Does anyone know the time complexity of the algorithm used to handle > > triggers with a when clause? > > It's done with a linear scan of all triggers, testing the WHEN clause > for

Re: [GENERAL] trigger when clause

On Tue, 2012-04-10 at 16:15 -0400, Andy Chambers wrote: > Does anyone know the time complexity of the algorithm used to handle > triggers with a when clause? It's done with a linear scan of all triggers, testing the WHEN clause for each. > To make this a little more concrete, what is likely to

Re: [GENERAL] Trigger.. AFTER and BEFORE with specific column changed

Albert writes: > *i get:* > Syntax error at or near 'WHEN' > LINE 1: ... check_update AFTER UPDATE ON accounts FOR EACH ROW WHEN > (OLD.balance IS DISTINCT FROM NEW.balance) Does the BEFORE case work for you either? I'm suspicious that you are using a PG version that predates WHEN-clause su

Re: [GENERAL] Trigger.. AFTER and BEFORE with specific column changed

On 04/01/2012 10:10 PM, Albert wrote: *i get:* Syntax error at or near 'WHEN' LINE 1: ... check_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) Hmmm. So two questions: What happens if you copy the example in the docs exactly and use NEW.* and

Re: [GENERAL] Trigger.. AFTER and BEFORE with specific column changed

*i get:* Syntax error at or near 'WHEN' LINE 1: ... check_update AFTER UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Trigger-AFTER-and-BEFORE-with-specific-column-changed-tp5610712p561

Re: [GENERAL] Trigger.. AFTER and BEFORE with specific column changed

On 04/01/2012 11:45 AM, Albert wrote: *I'm trying to follow this :* CREATE TRIGGER check_update *BEFORE* UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE PROCEDURE check_account_update(); *but with AFTER instead of BEFORE. and I'm keep

Re: [GENERAL] Trigger/Query Warnings

Hi David, Thanks, that's exactly what it was. Jake On 9 October 2011 10:05, David Johnston wrote: > On Oct 8, 2011, at 21:45, Jake Stride wrote: > >> Hi, >> >> I've been staring at this for hours and was hoping somebody could >> point me in the right direction. >> >> I have a trigger setup on

Re: [GENERAL] Trigger/Query Warnings

On Oct 8, 2011, at 21:45, Jake Stride wrote: > Hi, > > I've been staring at this for hours and was hoping somebody could > point me in the right direction. > > I have a trigger setup on a table to update some values based on the > values being inserted/updated and keep getting warning messages

Re: [GENERAL] trigger - dynamic WHERE clause

2011/5/31 Tarlika Elisabeth Schmitz : > On Tue, 31 May 2011 06:09:18 +0200 > Pavel Stehule wrote: > >>2011/5/31 Tarlika Elisabeth Schmitz : >>> On Mon, 30 May 2011 11:02:34 +0200 >>> Pavel Stehule wrote: >>> > 2) I took from your blog entry > (http://okbob.blogspot.com/2008/06/execute-usi

Re: [GENERAL] trigger - dynamic WHERE clause

On Tue, 31 May 2011 06:09:18 +0200 Pavel Stehule wrote: >2011/5/31 Tarlika Elisabeth Schmitz : >> On Mon, 30 May 2011 11:02:34 +0200 >> Pavel Stehule wrote: >> 2) I took from your blog entry (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html) that it is

Re: [GENERAL] trigger - dynamic WHERE clause

2011/5/31 Tarlika Elisabeth Schmitz : > On Mon, 30 May 2011 11:02:34 +0200 > Pavel Stehule wrote: > >>> 2) I took from your blog entry >>> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html) >>> that it is good practice to use EXECUTE USING. >>> Well, there's no danger

Re: [GENERAL] trigger - dynamic WHERE clause

On Mon, 30 May 2011 11:02:34 +0200 Pavel Stehule wrote: >> 2) I took from your blog entry >> (http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html) >> that it is good practice to use EXECUTE USING. >> Well, there's no danger of SQL injection as this particular DB runs >>

Re: [GENERAL] trigger - dynamic WHERE clause

Hello >>[...] >>Clause USING doesn't do a array unpacking >> >>you should to generate little bit different dynamic statement >>EXECUTE 'SELECT .. WHERE a = $1[1] AND b = $1[2]' USING ARRAY[...] > > I changed that but this wasn't my only problem; typecasting was the > second issue. Column "id1" is

Re: [GENERAL] trigger - dynamic WHERE clause

Hello Pavel, Thanks for taking the time to reply. On Fri, 27 May 2011 09:12:20 +0200 Pavel Stehule wrote: >Hello > >2011/5/26 Tarlika Elisabeth Schmitz : >> On Sun, 22 May 2011 20:39:01 +0200 >> Pavel Stehule wrote: >> >>>2011/5/22 Tarlika Elisabeth Schmitz >>>: EXECUTE 'SELECT 1 FROM ' ||

Re: [GENERAL] trigger - dynamic WHERE clause

On Sun, 22 May 2011 20:39:01 +0200 Pavel Stehule wrote: >Hello > >2011/5/22 Tarlika Elisabeth Schmitz : >> EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause >> || ' FOR UPDATE;'; >> >> I am generating the whereclause dynamically as the number of columns >> queried varies. >> >

Re: [GENERAL] trigger - dynamic WHERE clause

Hello 2011/5/22 Tarlika Elisabeth Schmitz : > EXECUTE 'SELECT 1 FROM ' || TG_TABLE_NAME || ' WHERE ' || whereclause > || ' FOR UPDATE;'; > > I am generating the whereclause dynamically as the number of columns > queried varies. > > Am I right in assuming that I cannot use EXECUTE ... USING in this

Re: [GENERAL] Trigger vs web service

If I was Yoda, I would say "The answer you seek is... it depends" If I were you, I would test both solutions and check which one performs better and impacts the least on your environment. For example, if you have no logic at all on the database then I would code that in the frontend. If you have

Re: [GENERAL] Trigger vs web service

I receive a long string (about 1 per second) than content many information. For the moment it is directly inserted in the database (1 column). I have to treat the hole string every time i need information in it. Now, I want split the sting and save the informations in differents fields. I have 2

Re: [GENERAL] Trigger vs web service

On 04/04/11 8:47 AM, Marc-André Goderre wrote: 1- Program a trigger function detecting the orginal insert, split the string and fill the other field. 2- Program a web service for receiving the string, split it and insert the informations in the db. Witch is the fastest one (in performance)

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.

Re: [GENERAL] Trigger problem, record "new" is not assigned yet

I'm very embarresed now. There were another trigger that caused a problem. Now it works. Thank you all for helping! :-) 2011/2/10 Adrian Klaver : > On Thursday, February 10, 2011 6:29:58 am A B wrote: >> Hello. >> >> I'm probably doing some very basic error here, but I get >> >> ERROR:  record "

Re: [GENERAL] Trigger problem, record "new" is not assigned yet

On Thu, Feb 10, 2011 at 9:38 AM, A B wrote: > CREATE TRIGGER trigger_foo BEFORE INSERT ON foo for each row EXECUTE > PROCEDURE trigger_foo(); > > gives me the same error. > Maybe "NEW" needs to be all caps? Also, with the BEFORE trigger, you'll need to RETURN NEW. This trigger of mine works jus

Re: [GENERAL] Trigger problem, record "new" is not assigned yet

On Thursday, February 10, 2011 6:29:58 am A B wrote: > Hello. > > I'm probably doing some very basic error here, but I get > > ERROR: record "new" is not assigned yet > The tuple structure of a not-yet-assigned record is indeterminate. > > when I try this small example > > create table foo(x

Re: [GENERAL] Trigger problem, record "new" is not assigned yet

Thanks for the suggestion, but CREATE TRIGGER trigger_foo BEFORE INSERT ON foo for each row EXECUTE PROCEDURE trigger_foo(); gives me the same error. 2011/2/10 Vick Khera : > On Thu, Feb 10, 2011 at 9:29 AM, A B wrote: >> Can someone help me spot the error? :-) >> > > use a BEFORE INSERT trigg

Re: [GENERAL] Trigger problem, record "new" is not assigned yet

On Thu, Feb 10, 2011 at 9:29 AM, A B wrote: > Can someone help me spot the error? :-) > use a BEFORE INSERT trigger? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

  1   2   3   4   5   >