Thread: Triggers question

Hi All!<br />I want to setup a trigger capable to return more than one record.<br /><br />Example (table users
contains10 records):<br /><br />CREATE FUNCTION get_users() RETURNS <br />SOME_TYPE AS '<br />BEGIN<br />       return
(SELECT* FROM users);<br />' LANGUAGE 'plpgsql';<br /><br />I can't figure out the correct Postgres type for SOME_TYPE
(seeabove example).<br /><br />How can I write such trigger? <br />I mean, what is the correct prototype of this
triggerfunction?<br /><br />Thanks in advance<br />/ycrux<br /><br /><br /><br /><br /><br /><br /> 

On Wed, Mar 01, 2006 at 02:22:15PM +0100, ycrux@club-internet.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