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

Great! That is what I need!

Thank you Pavel.

Best Regards,

On Sun, Jul 4, 2010 at 12:20 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> 2010/7/4 Andre Lopes <lopes80andre(at)gmail(dot)com>:
> > 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?
>
> CREATE OR REPLACE FUNCTION foo(...)
> RETURNS int AS $$
> BEGIN
> INSERT INTO ...
> RETURN 1
> EXCEPTION WHEN OTHERS THEN
> RETURN 0
> END
> $$ LANGUAGE plpgsql;
>
> But I am sure so you can see exception from php too.
>
> Regards
> Pavel
>
> >
> > 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é.
> >> >
> >
> >
>