From: | Andre Lopes <lopes80andre(at)gmail(dot)com> |
---|---|
To: | postgresql Forums <pgsql-general(at)postgresql(dot)org> |
Subject: | How to know if an INSERT is done inside a function? |
Date: | 2010-07-04 10:01:51 |
Message-ID: | AANLkTinHLwyt7bs6cNnlMiGlQKR1rwI9D0hqgmWVI4xO@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Pavel Stehule | 2010-07-04 10:05:57 | Re: How to know if an INSERT is done inside a function? |
Previous Message | Tom Lane | 2010-07-03 23:24:39 | Re: SQL statement logging: picking up strange queries from "pg_catalog" |