From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Andre Lopes <lopes80andre(at)gmail(dot)com> |
Cc: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: How to know if an INSERT is done inside a function? |
Date: | 2010-07-04 10:05:57 |
Message-ID: | AANLkTikhUObbXX42tgmLf8ddz4-0tmqjYqu-Ic5KTPxL@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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é.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andre Lopes | 2010-07-04 11:15:39 | Re: How to know if an INSERT is done inside a function? |
Previous Message | Andre Lopes | 2010-07-04 10:01:51 | How to know if an INSERT is done inside a function? |