[SQL] Get id of a tuple using exception
Hello, i'm using a pl/sql procedure and I prevent inserting duplicate tuples using an exception for example: BEGIN INSERT INTO Triples(id, subject, predicate, object) VALUES (id, sub_i, pred_i, obj_i); * EXCEPTION WHEN unique_violation THEN --do something. *In some cases I have interest in getting the id of the tuple that was already in the table when the exception is triggered. Is there a way for the EXCEPTION to return that id instead of using a select to know wich was the id of the triple already existing in the table? Thanks, Filipe
Re: [SQL] Get id of a tuple using exception
On Thursday, April 14, 2011 3:56:51 am f vf wrote: Hello, i'm using a pl/sql procedure and I prevent inserting duplicate tuples using an exception for example: BEGIN INSERT INTO Triples(id, subject, predicate, object) VALUES (id, sub_i, pred_i, obj_i); * EXCEPTION WHEN unique_violation THEN --do something. *In some cases I have interest in getting the id of the tuple that was already in the table when the exception is triggered. Is there a way for the EXCEPTION to return that id instead of using a select to know wich was the id of the triple already existing in the table? If the id is the PRIMARY KEY then it would be the same as the id you tried to INSERT correct? Thanks, Filipe -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get id of a tuple using exception
On Thursday, April 14, 2011 6:50:57 am f vf wrote: No, the id is nextval( 'triples_seq'), if I do nothing, so its something like: BEGIN INSERT INTO Triples(id, subject, predicate, object) VALUES (nextval( 'triples_seq'), sub_i, pred_i, obj_i); EXCEPTION WHEN unique_violation THEN --do something. The unique constraint is applyied to the subject, predicate and object fields. So, if I try to insert anything that has these 3 fields equal to any tuple that already exists in the table I want to get the id of the original tuple. To your original question I am not aware of a way of returning the id of the offending tuple, other than through a SELECT. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] update using recursion
Is it possible to execute an update using recursion? I need to update a set of records and also update their children with the same value. I tried the following query but it gave an error at the update schema.table tbl: with recursive childTbl( pid, ppid, proc_id, other_id ) as ( select prc.pid, prc.ppid, prc.proc_id, prc.other_id from my_schema.prc_tbl prc where ( ( prc.proc_path like '%stuff%' ) or ( prc.proc_parameters like '%stuff%' ) ) and ( prc.other_id is null ) union all select prcsub.pid, prcsub.ppid, prcsub.proc_id, prcsub.other_id from childTbl prcpar, my_schema.prc_tbl prcsub where ( prcsub.ppid = prcpar.pid ) ) update my_schema.prc_tbl prc set other_id = 101 from childTbl However, if I do a select * from childTbl it works. The docs take about updates and talk about recursive queries with selects but nothing seems to cover the joining of the two. Thanks -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
