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

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

From

Andre Lopes

Date:

04 July 2010, 10:02:02

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

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

From

Pavel Stehule

Date:

04 July 2010, 10:06:26

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

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

From

Andre Lopes

Date:

04 July 2010, 11:15:50

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?

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

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

From

Pavel Stehule

Date:

04 July 2010, 11:20:56

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

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

From

Andre Lopes

Date:

04 July 2010, 11:25:34

Great! That is what I need!

Thank you Pavel.

Best Regards,

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

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

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

From

Merlin Moncure

Date:

04 July 2010, 13:41:36

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.

merlin

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

From

Pavel Stehule

Date:

04 July 2010, 15:13:09

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
>

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

From

Andre Lopes

Date:

04 July 2010, 23:40:22

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
>

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

From

Ricardo Ramírez

Date:

05 July 2010, 01:29:36

Hi,
look at this page of the documentation of postgres http://www.postgresql.org/docs/current/static/plpgsql-statements.html
there explain some ways to know the effect of a SQL command.

Regards.

2010/7/4 Andre Lopes <lopes80andre@gmail.com>

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
>