Thread: get value after updating table

get value after updating table

From

"Alain Roger"

Date:

26 March 2007, 19:38:14

Hi,

I wrote a function which should update a table field.
However, i would like somehow to control that update was done.
for that i was thinking to return a boolean : true is update was done, false if an error happened.

however, i can i do that ? I mean how can i know if UPDATE has been correctly executed ?

here is my function :
CREATE OR REPLACE FUNCTION sp_a_005("login" "varchar", pwd "varchar")
RETURNS boolean AS
$BODY$

DECLARE

BEGIN
    UPDATE accounts
    SET account_pwd = $2
    WHERE account_login = $1;
RETURN;

END;

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

Re: get value after updating table

From

"Martin Gainty"

Date:

26 March 2007, 20:07:03

roger-

you need an exception block as in...

CREATE OR REPLACE FUNCTION sp_a_005("login" "varchar", pwd "varchar")
RETURNS boolean AS
$BODY$

BEGIN
UPDATE...

EXCEPTION
WHEN NO_DATA_FOUND

RETURN FALSE

WHEN PROGRAM_ERROR

RETURN FALSE

WHEN OTHERS

RETURN FALSE

RETURN TRUE

END;

---------------------------------------------------------------------------
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly prohibited.
---------------------------------------------------------------------------
Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiqué et peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document, nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.

----- Original Message -----
From: Alain Roger
To: pgsql-general@postgresql.org
Sent: Monday, March 26, 2007 3:38 PM
Subject: [GENERAL] get value after updating table

Hi,

I wrote a function which should update a table field.
However, i would like somehow to control that update was done.
for that i was thinking to return a boolean : true is update was done, false if an error happened.

however, i can i do that ? I mean how can i know if UPDATE has been correctly executed ?

here is my function :
CREATE OR REPLACE FUNCTION sp_a_005("login" "varchar", pwd "varchar")
RETURNS boolean AS
$BODY$

DECLARE

BEGIN
    UPDATE accounts
    SET account_pwd = $2
    WHERE account_login = $1;
RETURN;

END;

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

Re: get value after updating table

From

Martijn van Oosterhout

Date:

26 March 2007, 21:00:47

On Mon, Mar 26, 2007 at 09:38:07PM +0200, Alain Roger wrote:
> Hi,
>
> I wrote a function which should update a table field.
> However, i would like somehow to control that update was done.
> for that i was thinking to return a boolean : true is update was done, false
> if an error happened.

I beleive in recent versions you can do things like:
>    UPDATE accounts
>    SET account_pwd = $2
>    WHERE account_login = $1
RETURNING account_id;

If it updated it would return the ID of the row updated...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

signature.asc