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é.
>