[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
[SQL] update with recursive query
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_parameterslike '%stuff%' ) ) and ( prc.other_id is null ) union all select prcsub.pid, prcsub.ppid, prcsub.proc_id, prcsub.other_id from childTblprcpar, 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
[SQL] convert in GMT time zone without summer time
Hello,
I want write a function that converts a timestamp with time zone to the UTC
zone. But it should all be stored in the winter time.
For example, it must now, in the summer, the German time back by 2 hours and
in the winter time only 1 hour. But it expects only back one hour.
Is there a function or a specific time zone?
(I work with PostgreSQL 8.4.7)
[CODE]
CREATE OR REPLACE FUNCTION CONVERT_TO_UTC (TIMESTAMP with time zone,
VARCHAR)
returns TIMESTAMP
as $$
declare
v_zone VARCHAR(20);
p_time ALIAS FOR $1;
p_zone ALIAS FOR $2;
v_time1 TIMESTAMP with time zone;
v_time2 TIMESTAMP with time zone;
v_text1 text;
begin
IF LENGTH(p_zone) IS NULL THEN
v_zone := 'GMT';
else
v_zone := p_zone;
END IF;
-- ++ Timestamp with time zone in Text umwandeln
select to_char(p_time, 'DD Mon HH24:MI:SS') into v_text1;
if(v_zone in ('BST', 'CET', 'DNT', 'FST', 'MET', 'MEWT', 'MEZ', 'NOR',
'SET', 'SWT', 'WETDST')) then
-- ++ Timestamp with time zone in die Zeitzone '+01' umwandeln. ++
SET TIME ZONE 1;
Select CONVERT_TO_UTC_EXEC(v_text1, 'UTC') into v_time1;
elsif(v_zone in ('JST', 'KST', 'MHT', 'WDT', 'AWSST')) then
SET TIME ZONE 9;
-- ++ Timestamp with time zone in die Zeitzone '+09' umwandeln. ++
Select CONVERT_TO_UTC_EXEC(v_text1, 'UTC') into v_time1;
elsif(v_zone in ('GMT', 'UT', 'UTC', 'Z', 'ZULU', 'WET')) then
-- ++ Zone wird nicht geändert ++
v_time1 := p_time;
else
raise exception 'unbekannte Zone - ist noch eine Baustelle';
end if;
RETURN v_time1 ;
end
$$
LANGUAGE 'plpgsql';
[/CODE]
[CODE]
CREATE OR REPLACE FUNCTION CONVERT_TO_UTC_EXEC (Text, Text)
returns TIMESTAMP
as $$
declare
p_time ALIAS FOR $1;
p_zone ALIAS FOR $2;
v_time1 TIMESTAMP with time zone;
v_time2 TIMESTAMP with time zone;
v_text1 text;
begin
select to_timestamp (p_time, 'DD Mon HH24:MI:SS') into v_time1 ;
-- ++ Timestamp with time zone in die UTC Zeitzone umwandeln. ++
Select timezone( p_zone, v_time1) INTO v_time2 ;
-- ++ Zeitausgabe formatieren: HH12. ++
v_text1 := to_char(v_time2, 'DD Mon HH12:MI:SS AM');
-- ++ In Type Timestamp umwandeln. ++
RETURN to_timestamp( v_text1, 'DD Mon HH12:MI:SS AM') ;
end
$$
LANGUAGE 'plpgsql';
[/CODE]
calling:
[CODE]
SELECT
to_char(CONVERT_TO_UTC(to_timestamp('2011-03-22 14:17:00', '-MM-DD
hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS winter,
to_char(CONVERT_TO_UTC(to_timestamp('2011-04-22 14:17:00', '-MM-DD
hh24:MI:SS'), 'CET'), '-mm-dd hh24:MI:SS') AS summer
[/CODE]
must come out:
[CODE]
WINTER | SUMMER
+-
2011-03-22 13:17:00 | 2011-04-22 12:17:00
[/CODE]
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/convert-in-GMT-time-zone-without-summer-time-tp4304830p4304830.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.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] update with recursive query
Hello it is possible in 9.1. In older version you have to use a temp table. Regards Pavel Stehule 2011/4/14 Steven Dahlin : > 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
