From daveh@insightdist.com Thu Feb 11 23:03:09 1999 Received: from u1.abs.net (root@u1.abs.net [207.114.0.131]) by hub.org (8.9.2/8.9.1) with ESMTP id SAA79654 for ; Thu, 11 Feb 1999 18:03:09 -0500 (EST) (envelope-from daveh@insightdist.com) Received: from insightdist.com (nobody@localhost) by u1.abs.net (8.9.0/8.9.0) with UUCP id RAA29426 for pgsql-sql@postgreSQL.org; Thu, 11 Feb 1999 17:50:18 -0500 (EST) X-Authentication-Warning: u1.abs.net: nobody set sender to insightdist.com!daveh using -f Received: from ceodev by insightdist.com (AIX 3.2/UCB 5.64/4.03) id AA34275; Thu, 11 Feb 1999 17:28:58 -0500 Received: from daveh by ceodev (AIX 4.1/UCB 5.64/4.03) id AA21150; Thu, 11 Feb 1999 17:28:58 -0500 Message-Id: <36C359C7.60932427@insightdist.com> Date: Thu, 11 Feb 1999 17:29:27 -0500 From: David Hartwig Organization: Insight Distribution Systems X-Mailer: Mozilla 4.5 [en] (Win98; I) X-Accept-Language: en Mime-Version: 1.0 To: pgsql-sql Subject: Plpgsql Language Feature? Content-Type: multipart/alternative; boundary="------------AAFD4B2E12D16A77A0052B93" --------------AAFD4B2E12D16A77A0052B93 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit In the context of a plpgsql script is a possible to access the number of rows effected by an UPDATE or DELETE statement? It could be used to simplify, and probably improve performance on, many scripts. I would like to do something like: update foo set bar = bar + new.amount where key = new.key; if ROWS_UPDATED > 0 then -- This would be less likely to occur insert into foo (key, bar) values (new.key, new.bar); end if; Otherwise I must construct an additional SELECTstatement to see if the row exists. Something like: select 1 from foo where key = new.key; if found then update foo set bar = bar + new.amount where key = new.key; else insert into foo (key, bar) values (new.key, new.bar); end if; Might this be a worth while feature if it does not now exist? TIA. --------------AAFD4B2E12D16A77A0052B93 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit In the context of a plpgsql script is a possible to access the number of rows effected by an UPDATE or DELETE statement?   It could be used  to simplify, and probably improve performance on, many scripts.   I would like to do something like:

    update foo set bar = bar + new.amount where key = new.key;

    if ROWS_UPDATED > 0 then
        --  This would be less likely to occur
        insert into foo (key, bar) values (new.key, new.bar);
    end if;

Otherwise I must construct  an additional SELECTstatement to see if the row exists.  Something like:

    select  1 from foo where key = new.key;
 
    if found then
        update foo set bar = bar + new.amount where key = new.key;
    else
        insert into foo (key, bar) values (new.key, new.bar);
    end if;

Might this be a worth while feature if it does not now exist?

TIA. --------------AAFD4B2E12D16A77A0052B93-- From jwieck@debis.com Fri Feb 12 01:02:06 1999 Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8]) by hub.org (8.9.2/8.9.1) with SMTP id UAA92815 for ; Thu, 11 Feb 1999 20:02:06 -0500 (EST) (envelope-from wieck@sapserv.debis.de) Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-sql@postgreSQL.org id m10B768-000EBRC; Fri, 12 Feb 99 02:08 MET Message-Id: From: jwieck@debis.com (Jan Wieck) Subject: Re: [SQL] Plpgsql Language Feature? To: daveh@insightdist.com (David Hartwig) Date: Fri, 12 Feb 1999 02:08:47 +0100 (MET) Cc: pgsql-sql@postgreSQL.org Reply-To: jwieck@debis.com (Jan Wieck) In-Reply-To: <36C359C7.60932427@insightdist.com> from "David Hartwig" at Feb 11, 99 05:29:27 pm X-Mailer: ELM [version 2.4 PL25] Content-Type: text > In the context of a plpgsql script is a possible to access the number of > rows effected by an UPDATE or DELETE statement? It could be used to > simplify, and probably improve performance on, many scripts. I would > like to do something like: > > update foo set bar = bar + new.amount where key = new.key; > > if ROWS_UPDATED > 0 then > -- This would be less likely to occur > insert into foo (key, bar) values (new.key, new.bar); > end if; Yes, I think this would be a nice feature. Will take a look if I can add it before v6.5 BETA starts. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #======================================== jwieck@debis.com (Jan Wieck) #