Re: pl/PgSQL: Samples doing UPDATEs ...

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