Re: hi let me know the solution to this question - Mailing list pgsql-sql

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