pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpl/PgSQL: Samples doing UPDATEs ...
3+ messages / 3 participants
[nested] [flat]
* pl/PgSQL: Samples doing UPDATEs ...
@ 2005-08-19 05:38 Marc G. Fournier <scrappy@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: Marc G. Fournier @ 2005-08-19 05:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pl/PgSQL: Samples doing UPDATEs ...
@ 2005-08-19 06:32 daq <daq@ugyvitelszolgaltato.hu>
parent: Marc G. Fournier <scrappy@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: daq @ 2005-08-19 06:32 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
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/interactive/plpgsql-statements.html
DAQ
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pl/PgSQL: Samples doing UPDATEs ...
@ 2005-08-19 06:33 Michael Fuhr <mike@fuhr.org>
parent: Marc G. Fournier <scrappy@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-08-19 06:33 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@postgresql.org>; +Cc: pgsql-sql
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/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-19 06:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-19 05:38 pl/PgSQL: Samples doing UPDATEs ... Marc G. Fournier <scrappy@postgresql.org>
2005-08-19 06:32 ` daq <daq@ugyvitelszolgaltato.hu>
2005-08-19 06:33 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox