Обсуждение: Capturing return value of a function

Hi,

I have a function like this


CREATE OR REPLACE FUNCTION sp_insert(text, text, text, text)
  RETURNS text AS
$BODY$
    BEGIN
        INSERT INTO tbl(a,b,c,d)
        VALUES ($1,$2, $3, $4);

        RETURN 'success';
    END;
       
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

I was wondering what would the function return if insert fails.
I want it to return 'success'  upon a successful insert and 'failure' if insert fails.

Would the following implementation work for that?


CREATE OR REPLACE FUNCTION sp_insert(text, text, text, text)
  RETURNS text AS
$BODY$
    BEGIN
                    BEGIN
                            INSERT INTO tbl(a,b,c,d)
                            VALUES ($1,$2, $3, $4);
                            
                                    RETURN 'success';
                    END;

                      RETURN 'failure';
    END;
       
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

On Jul 16, 2007, at 15:55 , Jasbinder Singh Bali wrote:

> I was wondering what would the function return if insert fails.
> I want it to return 'success'  upon a successful insert and
> 'failure' if
> insert fails.

Depends on what you mean by "failed". Do you mean an error was
raised? Then you'll just get a normal error message. To catch this,
check the error trapping section of the PL/pgSQL documentation.

Do you mean nothing was inserted? If so, you won't catch this in your
above code. You can check the FOUND special variable after the INSERT
to see if anything was inserted and set your result message based on
that.

> Would the following implementation work for that?

No. BEGIN/END blocks in PL/pgSQL are not equivalent to SQL BEGIN/
COMMIT transactions.


Michael Glaesemann
grzm seespotcode net

"Jasbinder Singh Bali" <jsbali@gmail.com> writes:

> I was wondering what would the function return if insert fails.

It would abort immediately and abort your transaction as well unless something
higher up catches the error.

> I want it to return 'success'  upon a successful insert and 'failure' if
> insert fails.

You need an EXCEPT WHEN clause, see:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

----- Original Message -----
From: Jasbinder Singh Bali
To: pgsql-general@postgresql.org
Sent: Monday, July 16, 2007 3:55 PM
Subject: [GENERAL] Capturing return value of a function

<snip>

> I was wondering what would the function return if insert fails.

> I want it to return 'success'  upon a successful insert and 'failure' if
> insert fails.

You can find out whether the insert was successful or not using the tools
here:

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS


<snip>

Regards,
George