Re: [SQL] update with join
On Wed, 2 Apr 2008 23:54:18 -0300 Osvaldo Kussama [EMAIL PROTECTED] wrote: 2008/4/2, Ivan Sergio Borgonovo [EMAIL PROTECTED]: I've create table types( typeid int, special boolean not null ); create table methods( methodid int, typeid references types(typeid), ); create table orders( orderid int ); create table order_payments( payid int orderid references order(orderid), methodid references method(methodid), issued boolean not null default false ); orderid payid methodid special 1 1 1t 1 2 2t 1 3 3t 1 4 4f 1 5 4f I'd like to chose one payid If the payid is special just set issued to true for that payid, leave the other unchanged. If the payid is not special set issued for all the payid in the same order. eg. So if payid=4 I'd have orderid payid methodid special issued 1 1 1t t 1 2 2t t 1 3 3t t 1 4 4f t 1 5 4f t and if payid=2 orderid payid methodid special issued 1 1 1t f 1 2 2t t 1 3 3t f 1 4 4f f 1 5 4f f This stuff below doesn't work: update order_payments set issued=true where payid in ( select p.payid from order_payments p join methods as m on m.methodid=p.methodid join types as t on m.typeid=t.typeid where (p.orderid=%d and not t.special) or p.payid=%d); and I can understand why but I can't rewrite it to make it work. Try: UPDATE order_payments SET issued=true FROM methods m, types t WHERE m.methodid=p.methodid AND p - order_payments m.typeid=t.typeid AND ((order_payments.orderid=%d AND NOT t.special) OR order_payments.payid=%d)); one less ) Even after correcting the few typos this version obtain the same result of update order_payments set issued=true where payid=%d I ended up in writing a plpgsql function that retrieve special and then have an if block. create or replace function IssuePay(_PayID int, out _OrderGroupID bigint, out _Online boolean) as $$ begin select into _OrderGroupID, _OnLine p.OrderGroupID, t.OnLine from shop_commerce_ordergroup_pay p join shop_commerce_paymethods m on p.PayMethodID=m.MethodID join shop_commerce_paytypes t on m.TypeID=t.TypeID where PayID=_PayID; if(_OnLine) then update shop_commerce_ordergroup_pay set Issued=true where PayID=_PayID; else update shop_commerce_ordergroup_pay set Issued=true where OrderGroupID=_OrderGroupID; end if; return; end; $$ language plpgsql; mutatis mutandis. It may not be the most elegant thing but it is enough encapsulated it won't be a pain to refactor once I become a better DBA or someone else point out a better solution on the list. I'd be curious if it had a performance penalty over a one update statement. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] BROBLEM IN BETWEEN QUERY (plpgsql)
hai all, I have a plpgsql function and I am using postgresl 8.1.9 CREATE OR REPLACE FUNCTION get_vehicle_id(INT) RETURNS SETOF RECORD AS $BODY$ DECLARE r RECORD; int_day ALIAS FOR $1; BEGIN FOR r in SELECT fk_bint_old_vehicle_number AS vehicle_id FROM tbl_rac_vehicle_replacement WHERE dat_replacement BETWEEN now() - interval '% day',int_day AND now() LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'; But i cant craete the function The error message is: ERROR: syntax error at or near , at character 137 QUERY: SELECT fk_bint_old_vehicle_number AS vehicle_id FROM tbl_rac_vehicle_replacement WHERE dat_replacement BETWEEN now() - interval '% day', $1 AND now() CONTEXT: SQL statement in PL/PgSQL function test near line 11 LINE 1: ...E dat_replacement BETWEEN now() - interval '% day', $1 AND ... The problem line is BETWEEN now() - interval '% day', $1 AND now() I want to select dat_replacement between now () and now - 5 dyas or now -7 days like that i want to pass the integer value as argument to the function. I also tried like this WHERE dat_replacement BETWEEN now() - interval int_day day AND now(),but it also failed. Please help me . regards: Anoop
Re: [SQL] BROBLEM IN BETWEEN QUERY (plpgsql)
am Thu, dem 03.04.2008, um 15:54:56 +0530 mailte Anoop G folgendes: hai all, I want to select dat_replacement between now () and now - 5 dyas or now -7 days like that i want to pass the integer value as argument to the function. I show you a similar solution: test=*# select * from foo; i | ts ---+--- 1 | 2008-03-31 15:11:36.214272+02 (1 row) test=*# create or replace function f1(in i int, out t timestamptz) returns setof timestamptz as $$ declare r record;s timestamptz; begin s:=current_timestamp-i * '1day'::interval; for r in select * from foo where ts between s and current_timestamp loop raise notice '-- %',$1; t:=r.ts; return next; end loop; raise notice '%',s; end; $$ language plpgsql; CREATE FUNCTION test=*# select * from f1(1); NOTICE: 2008-04-02 13:05:08.48866+02 t --- (0 rows) test=*# select * from f1(10); NOTICE: -- 10 NOTICE: 2008-03-24 13:05:08.48866+01 t --- 2008-03-31 15:11:36.214272+02 (1 row) More examples with IN/OUT - parameters: http://people.planetpostgresql.org/xzilla/index.php?/archives/149-out-parameter-sql-plpgsql-examples.html#extended HTH, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] GiST/GIN index for field of type VARCHAR[]
I have following table: CREATE TABLE t1 ( name VARCHAR(500) NOT NULL, lid INTEGER NOT NULL, accs VARCHAR(20)[] NOT NULL CONSTRAINT t1_lid_key UNIQUE(lid) ); I interested in the possibility to speed-up search for rows like this: SELECT lid FROM t1 WHERE accs ARRAY['item1','item2'...]::VARCHAR[]; For sure, I can use the typical way of data normalization to decrease query time: CREATE TABLE t2( lid INTEGER NOT NULL, acc VARCHAR(20) NOT NULL ); with: t2.lid = t1.lid t1.accs @ ARRAY[t2.acc] and create hash index on acc. Then I could SELECT lid FROM t2 WHERE acc IN(item1,item2); But it's more interesting to implement GiST/GIN indexes for this purpose. And what type of index is the most suitable if VARCHAR[] arrays are 1-dimensional and contain from 1 to 2 elements? Thanks in advance. -- Best regards. Yuri. mailto: [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] connections between servers
Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows conneciotns between different databases, but what about different servers? I also thought about using perl, would it be possible to connect to different server from within perl trigger? Thanks in advance. regards mk
Re: [SQL] apparent RI bug
On Wed, 2 Apr 2008, chester c young wrote: it appears I have a broken RI in my db. call_individual.clh_id references call_household.clh_id \d call_individual ... Foreign-key constraints: call_individual_clh_id_fkey FOREIGN KEY (clh_id) REFERENCES call_household(clh_id) ON DELETE CASCADE however: development=# select clh_id from call_individual cli where not exists( select 1 from call_household clh where clh.clh_id=cli.clh_id ); clh_id 14691 should not matter, but call_individual has a pre-delete trigger that simply raises an exception to prevent deletions: raise exception 'calls may not be deleted'; Yeah, that looks pretty broken. Can you reproduce this from a clean start repeatedly or is this a one off? Do you ever turn off triggers, perhaps by modifying the pg_class row's reltriggers (I'd guess the answer is no, but it'd be good to make sure)? -- 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] connections between servers
Marcin Krawczyk wrote: Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows conneciotns between different databases, but what about different servers? I also thought about using perl, would it be possible to connect to different server from within perl trigger? Thanks in advance. regards mk Yes it is possible. A PL/Perl trigger is one option you have. dblink (in contrib) and dbi-link, dblink-tds, oralink and odbclink are other options at pgfoundry.org depending on your needs. There was a recent discussion in the general mailing list about this. http://archives.postgresql.org/pgsql-general/2008-03/msg01343.php -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] apparent RI bug
Stephan Szabo [EMAIL PROTECTED] wrote: On Wed, 2 Apr 2008, chester c young wrote: it appears I have a broken RI in my db. Yeah, that looks pretty broken. Can you reproduce this from a clean start repeatedly or is this a one off? Do you ever turn off triggers, perhaps by modifying the pg_class row's reltriggers (I'd guess the answer is no, but it'd be good to make sure)? only one error. unable to duplicate so far. this is a development db - triggers are frequently dropped and created, but I don't think ever concurrently with db activity. You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.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] apparent RI bug
On Thu, 3 Apr 2008, chester c young wrote: Stephan Szabo [EMAIL PROTECTED] wrote: On Wed, 2 Apr 2008, chester c young wrote: it appears I have a broken RI in my db. Yeah, that looks pretty broken. Can you reproduce this from a clean start repeatedly or is this a one off? Do you ever turn off triggers, perhaps by modifying the pg_class row's reltriggers (I'd guess the answer is no, but it'd be good to make sure)? only one error. unable to duplicate so far. this is a development db - triggers are frequently dropped and created, but I don't think ever concurrently with db activity. Is it possible you ever had a before delete trigger that just did a return NULL rather than raising an exception? IIRC, explicitly telling the system to ignore the delete will work on the referential actions. -- 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] connections between servers
Thanks a lot, I'll give it a try. regards mk 2008/4/3, Shane Ambler [EMAIL PROTECTED]: Marcin Krawczyk wrote: Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows conneciotns between different databases, but what about different servers? I also thought about using perl, would it be possible to connect to different server from within perl trigger? Thanks in advance. regards mk Yes it is possible. A PL/Perl trigger is one option you have. dblink (in contrib) and dbi-link, dblink-tds, oralink and odbclink are other options at pgfoundry.org depending on your needs. There was a recent discussion in the general mailing list about this. http://archives.postgresql.org/pgsql-general/2008-03/msg01343.php -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Re: [SQL] apparent RI bug
--- Stephan Szabo [EMAIL PROTECTED] wrote: Is it possible you ever had a before delete trigger that just did a return NULL rather than raising an exception? IIRC, explicitly telling the system to ignore the delete will work on the referential actions. yes, it is possible, for example, a function without a body or without a return old. are you saying this would override the RI constraint? if so, is this by design? You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.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] apparent RI bug
On Thu, 3 Apr 2008, chester c young wrote: --- Stephan Szabo [EMAIL PROTECTED] wrote: Is it possible you ever had a before delete trigger that just did a return NULL rather than raising an exception? IIRC, explicitly telling the system to ignore the delete will work on the referential actions. yes, it is possible, for example, a function without a body or without a return old. are you saying this would override the RI constraint? If it returned something that would have prevented the delete without an error, yes. if so, is this by design? It's basically an ongoing question (without concensus AFAIK) about whether a rule or trigger should be allowed to stop the referential action and what should happen if it does. -- 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] apparent RI bug
--- Stephan Szabo [EMAIL PROTECTED] wrote: is it is possible, for example, a function without a body or without a return old. are you saying this would override the RI constraint? If it returned something that would have prevented the delete without an error, yes. this is very good news that there is a reason why the RI did not work, which is to say, RI not working randomly is very frightening if so, is this by design? It's basically an ongoing question (without concensus AFAIK) about whether a rule or trigger should be allowed to stop the referential action and what should happen if it does. in my opinion the most important thing is that it's documented. btw, cheers! you're my hero of the week!! You rock. That's why Blockbuster's offering you one month of Blockbuster Total Access, No Cost. http://tc.deals.yahoo.com/tc/blockbuster/text5.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
