Re: How to know if an INSERT is done inside a function?

Hello

every unsuccessful SQL command raises exception in PL/pgSQL. So if
your function is finished without exception, then INSERT is ok.

Regards

Pavel Stehule

2010/7/4 Andre Lopes <lopes80andre@gmail.com>:

> Hi,
>
> I have a function, at the end I need to know if the INSERTS have run
> successfully or not.
>
> Here is the function:
>
> [code]
> CREATE OR REPLACE FUNCTION "apr_insert_utilizador_ap"
> (IN ppgroup_id int4,
> IN ppip_address char,
> IN ppusername varchar,
> IN pppassword varchar,
> IN ppemail varchar,
> IN ppactive int4,
> IN ppnome_real varchar,
> IN pptelefone_pessoal varchar,
> IN ppid_anunciante varchar,
> OUT ppreturn_value int4
> )
> RETURNS int4 AS
> $BODY$
>
>     DECLARE
>     pGROUP_ID                 alias for $1;
>     pIP_ADDRESS                alias for $2;
>     pUSERNAME                alias for $3;
>     pPASSWORD                alias for $4;
>     pEMAIL                    alias for $5;
>     pACTIVE                    alias for $6;
>     pNOME_REAL                alias for $7;
>     pTELEFONE_PESSOAL        alias for $8;
>     pID_ANUNCIANTE            alias for $9;
>     vID_UTILIZADOR_MAX            int4;
>     vID_UTILIZADOR_NOVO            int4;
>     vRETURN                        int4;
>
>     BEGIN
>
>     SELECT INTO vID_UTILIZADOR_MAX max(id) AS max_id FROM aau_utilizadores;
>     vID_UTILIZADOR_NOVO := vID_UTILIZADOR_MAX + 1;
>
>     INSERT INTO aau_utilizadores
>     (id, group_id, ip_address, username, password, salt, email,
> activation_code,
>     forgotten_password_code, remember_code, created_on, last_login, active)
>     VALUES (vID_UTILIZADOR_NOVO, pGROUP_ID, pIP_ADDRESS, pUSERNAME,
> pPASSWORD, null,
>     pEMAIL, null, null, null, NOW(), null, pACTIVE);
>
>     INSERT INTO aau_metadata
>     (id, user_id, nome_real, telefone_pessoal)
>     VALUES (vID_UTILIZADOR_NOVO, vID_UTILIZADOR_NOVO, pNOME_REAL,
> pTELEFONE_PESSOAL);
>
>     INSERT INTO aau_anunciantes
>     (user_id, id_anunciante)
>     VALUES (vID_UTILIZADOR_NOVO, pID_ANUNCIANTE);
>
>     vRETURN := 1;
>     ppreturn_value := vRETURN;
>
>     END;
> $BODY$
>     LANGUAGE PLpgSQL
>     RETURNS NULL ON NULL INPUT
>     VOLATILE
>     EXTERNAL SECURITY INVOKER;
> [/code]
>
> If the INSERTS are all done the function RETURNS "1", but how can I RETURN
> "0" if any error occurred?
>
>
> Best Regards,
> André.
>