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

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é.

Responses

Browse pgsql-general by date

  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"