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

Hi,

Thanks for your reply.

Yes, in the Postgre command line I see the exception, the problem is that
I'am using this function in a PHP code. I need send the value "1" to the OUT
parameter if the function is successful or send the value "0" to the OUT
parameter if the function not runs successful.

How can I do this?

Best Regards,

On Sun, Jul 4, 2010 at 11:05 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> 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(at)gmail(dot)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é.
> >
>