PostgreSQL SQL - pl/PgSQL: Samples doing UPDATEs ...

Author

pl/PgSQL: Samples doing UPDATEs ...

Marc G. Fournier

2005-08-19, 3:25 am

I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it,
but no good samples ....

What I'm looking for is a sample of a function that returns # of rows
updated, so that I can make a decision based on that ... does anyone know
where I could find such (and others, would be great) online?

Thanks ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Fuhr

2005-08-19, 3:25 am

On Fri, Aug 19, 2005 at 02:38:01AM -0300, Marc G. Fournier wrote:
> I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it,
> but no good samples ....
>
> What I'm looking for is a sample of a function that returns # of rows
> updated, so that I can make a decision based on that ... does anyone know
> where I could find such (and others, would be great) online?

Are you looking for GET DIAGNOSTICS?

http://www.postgresql.org/docs/8.0/...NTS-DIAGNOSTICS

CREATE TABLE foo (id integer, name text);
INSERT INTO foo VALUES (1, 'Bob');
INSERT INTO foo VALUES (2, 'Bob');
INSERT INTO foo VALUES (3, 'Jim');

CREATE FUNCTION update_foo(old_name text, new_name text) RETURNS integer AS $$
DECLARE
num_rows integer;
BEGIN
UPDATE foo SET name = new_name WHERE name = old_name;
GET DIAGNOSTICS num_rows = ROW_COUNT;
RETURN num_rows;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

SELECT update_foo('Bob', 'Robert');
update_foo
------------
2
(1 row)

SELECT update_foo('Jim', 'James');
update_foo
------------
1
(1 row)

SELECT update_foo('Rick', 'Richard');
update_foo
------------
0
(1 row)

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

daq

2005-08-19, 3:25 am

MGF> I have an older PostgreSQL book here, that has chapter on pl/PgSQL in it,
MGF> but no good samples ....

MGF> What I'm looking for is a sample of a function that returns # of rows
MGF> updated, so that I can make a decision based on that ... does anyone know
MGF> where I could find such (and others, would be great) online?

....
execute ''Update ...'';
GET DIAGNOSTICS processed_rows = ROW_COUNT;
return processed_roows;
....

See PostgreSQL 7.3 online documentation 19.5.5. Obtaining result
status.
http://www.postgresql.org/docs/7.3/...statements.html

DAQ

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Sponsored Links