X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9DD8D9F9F27 for ; Tue, 18 Jul 2006 13:32:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 49506-02-6 for ; Tue, 18 Jul 2006 13:32:00 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 03A249FB388 for ; Tue, 18 Jul 2006 12:54:18 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id c29so202170nfb for ; Tue, 18 Jul 2006 08:54:12 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=kcJ7tSQfgtrS/dsNwkYCFJGY6BjL0sgEiyNw2pRuNFF1gTBhJoDRv/gciPHCmXJscQuq8ENQzw4ss9s3U3RWyoPbgcMKgmI/oFjcZeONCKkCjLl9wD/ZLmKFbDoKG3z+LEu9dd+RHKrkjvX5jyGchNmMrc8TVVoo11OFUxEI2qw= Received: by 10.78.151.3 with SMTP id y3mr1625835hud; Tue, 18 Jul 2006 08:54:12 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Tue, 18 Jul 2006 08:54:12 -0700 (PDT) Message-ID: Date: Tue, 18 Jul 2006 10:54:12 -0500 From: "Aaron Bono" To: "Michael Fuhr" Subject: Re: hi let me know the solution to this question Cc: "Penchalaiah P." , pgsql-sql@postgresql.org In-Reply-To: <20060718053442.GA9694@winnie.fuhr.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_27372_16684914.1153238052499" References: <20060718053442.GA9694@winnie.fuhr.org> X-Google-Sender-Auth: aba1acd920c64044 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/230 X-Sequence-Number: 25804 ------=_Part_27372_16684914.1153238052499 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/18/06, Michael Fuhr wrote: > > On Tue, Jul 18, 2006 at 10:46:29AM +0530, Penchalaiah P. wrote: > > Can u let me know if at all any system defined variable exists to check > > whether insert , update , and delete is successfully executed in a > > stored procedure........ > > > > So that I can perform some steps based on the result of execution if > > possible please send me the sample code............... > > Depending on what you mean by "successfully executed," see "Obtaining > the Result Status" or "Trapping Errors" in the PL/pgSQL documentation > (error trapping is available in 8.0 and later). > > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS > > http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING OK, this question got me wondering: is there a way to determine, in a function/stored procedure, the number of rows that were inserted/updated/deleted? For example, the following does NOT work but shows what I am trying to achieve: CREATE OR REPLACE FUNCTION "public"."test_fn" () RETURNS VOID AS ' DECLARE mycount INTEGER; BEGIN -- This assignment will not work be I want something like it -- so I can count the number of records updated. mycount := EXECUTE ''update mytable '' || ''mycolumn = 1 '' || ''WHERE '' || '' mycolumn = 2 '' ; RAISE NOTICE ''count = %'', mycount; RETURN; END; ' LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; I could not find any documentation about getting the count of updates but when you run the update by itself, PostgreSQL reports the number of records updated so I have to believe the information is available somewhere. Thanks, Aaron ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com ================================================================== ------=_Part_27372_16684914.1153238052499 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/18/06, Michael Fuhr <mike@fuhr.org> wrote:
On Tue, Jul 18, 2006 at 10:46:29AM +0530, Penchalaiah P. wrote:
> Can u let me know if at all any system defined variable exists to check
> whether insert , update , and delete is successfully executed in a
> stored procedure........
>
> So that I can perform some steps based on the  result of execution if
> possible please send me the sample code...............

Depending on what you mean by "successfully executed," see "Obtaining
the Result Status" or "Trapping Errors" in the PL/pgSQL documentation
(error trapping is available in 8.0 and later).

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

 
OK, this question got me wondering: is there a way to determine, in a function/stored procedure, the number of rows that were inserted/updated/deleted?

For example, the following does NOT work but shows what I am trying to achieve:

CREATE OR REPLACE FUNCTION "public"."test_fn" () RETURNS VOID AS
'
DECLARE
    mycount INTEGER;
BEGIN
    -- This assignment will not work be I want something like it
    -- so I can count the number of records updated.
    mycount := EXECUTE
        ''update mytable '' ||
        ''mycolumn = 1 '' ||
        ''WHERE '' ||
        ''    mycolumn = 2 ''
    ;
   
    RAISE NOTICE ''count = %'', mycount;
   
    RETURN;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

I could not find any documentation about getting the count of updates but when you run the update by itself, PostgreSQL reports the number of records updated so I have to believe the information is available somewhere.

Thanks,
Aaron

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
================================================================== ------=_Part_27372_16684914.1153238052499--