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

2010/7/4 Andre Lopes <lopes80andre@gmail.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.stehule@gmail.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@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é.
>> >
>
>