Thread: Questions about writing functions...

If I write a function that deletes a record from a table, how do I access
the output from the delete operation to see if it completed successfully?
Check out the IF statement below.

CREATE FUNCTION del_test(INT4)
        RETURNS INT4
        AS 'DECLARE
                int_input         ALIAS FOR $1;
                int_success    INT4;

            BEGIN
                int_success := 0;
                DELETE FROM table WHERE id = int_input;
                IF (output from delete operation = 1) THEN
                    int_success := 1
                END IF;
            RETURN int_success;
            END;'
LANGUAGE 'plpgsql';

I'm also wondering if I need to be able to trap an error and rollback the
state of the database. If so, how would I do it?
I've seen it done like this in other databases before:

EXEC SQL WHENEVER SQLERROR ROLLBACK ABORT;

Thanks, Bob

"Bob Whitehouse" <bwhitehouse@geeknest.com> writes:
> If I write a function that deletes a record from a table, how do I access
> the output from the delete operation to see if it completed successfully?

In 7.1 you could look at the ROW_COUNT statistic field to see how many
rows were deleted.  I don't think pre-7.1 plpgsql has a way to do that.

            regards, tom lane

> > If I write a function that deletes a record from a table, how do I
access
> > the output from the delete operation to see if it completed
successfully?
>
> In 7.1 you could look at the ROW_COUNT statistic field to see how many
> rows were deleted.  I don't think pre-7.1 plpgsql has a way to do that.

    Are there any plans to suport SQLERROR and the SQLCA area, or
similar concepts as described in
http://technet.oracle.com/doc/oracle8i_816/appdev.816/a76942/pc_09err.htm#30
49 or
http://technet.oracle.com/doc/oracle8i_816/appdev.816/a76975/oci02bas.htm#42
5597?

    I tried checking the docs for 7.1 in the Users' Lounge but my access
to them is *very* slow.



--
  _
 / \       Leandro Guimarães Faria Corcete Dutra        +55 (11) 3040 8913
 \ /       Amdocs Brasil Ltda at Tele Danmark                +45 3387 5214
  X   http://terravista.pt./Enseada/1989/     mailto:leandrod@amdocs.com
 / \     Campanha fita ASCII                        mailto:moreno@tdk.dk