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

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

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

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

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

merlin

2010/7/4 Merlin Moncure <mmoncure@gmail.com>:

On Sun, Jul 4, 2010 at 7:25 AM, Andre Lopes <lopes80andre@gmail.com>

wrote:

Great! That [begin exception end] is what I need!

Thank you Pavel.

Best Regards,

Just a quick heads up: functions with exception handlers tend to be
more expensive than those without, even if the exception doesn't fire.

yes, better to use prepared statement and read diagnostics info from PHP

Pavel

merlin

Hi,

The function will run in the php-cli, in a CronJob, it is not for use in a
PHP webpage. I think doesn't matter the extra time that takes to run.

Best Regards,

On Sun, Jul 4, 2010 at 4:11 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

2010/7/4 Merlin Moncure <mmoncure@gmail.com>:

On Sun, Jul 4, 2010 at 7:25 AM, Andre Lopes <lopes80andre@gmail.com>

wrote:

Great! That [begin exception end] is what I need!

Thank you Pavel.

Best Regards,

Just a quick heads up: functions with exception handlers tend to be
more expensive than those without, even if the exception doesn't fire.

yes, better to use prepared statement and read diagnostics info from PHP

Pavel

merlin