Re: Triggers question

On Wed, Mar 01, 2006 at 02:22:15PM +0100, ycrux(at)club-internet(dot)fr wrote:
> I want to setup a trigger capable to return more than one record.

Your example doesn't show anything related to triggers so I think
you mean "function" instead of "trigger." If the function can
return more than one row then it's a "set-returning" function.

> Example (table users contains 10 records):
> CREATE FUNCTION get_users() RETURNS
> SOME_TYPE AS '
> BEGIN
> return (SELECT * FROM users);
> ' LANGUAGE 'plpgsql';
> I can't figure out the correct Postgres type for SOME_TYPE (see above
> example).

This example's return type would be "SETOF users". This particular
function would be simpler in SQL than in PL/pgSQL:

CREATE FUNCTION get_users() RETURNS SETOF users AS '
SELECT * FROM users;
' LANGUAGE sql STABLE;

Here's the PL/pgSQL version:

CREATE FUNCTION get_users() RETURNS SETOF users AS '
DECLARE
row users%ROWTYPE;
BEGIN
FOR row IN SELECT * FROM users LOOP
RETURN NEXT row;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql STABLE;

You'd call the function as:

SELECT * FROM get_users();

For more information see "SQL Functions Returning Sets" (for SQL)
and "Control Structures" (for PL/pgSQL) in the documentation. Here
are links to the documentation for 8.1, but use the documentation
for whatever version you're running:

http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html#AEN31646
http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html

--
Michael Fuhr