From: Michael Fuhr <mike@fuhr.org>
To: Marc G. Fournier <scrappy@postgresql.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: pl/PgSQL: Samples doing UPDATEs ...
Date: Fri, 19 Aug 2005 00:33:10 -0600
Message-ID: <20050819063310.GA78746@winnie.fuhr.org> (raw)
In-Reply-To: <20050819023618.K1002@ganymede.hub.org>
References: <20050819023618.K1002@ganymede.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, scrappy@postgresql.org
Subject: Re: pl/PgSQL: Samples doing UPDATEs ...
In-Reply-To: <20050819063310.GA78746@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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