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