Re: Triggers in Postgres

[Roman Neuhauser <neuhauser@xxxxxxxxxx>]

# jsbali@xxxxxxxxx / 2006-08-01 02:35:48 -0400:
> On 8/1/06, Roman Neuhauser <neuhauser@xxxxxxxxxx> wrote:
> >
> ># jsbali@xxxxxxxxx / 2006-07-31 11:58:49 -0400:
> >> Actually Postgres manual of triggers says that in postgres, you can't
> >write
> >> a trigger in conventional sql. You have to write it in a procedural
> >language
> >> like C. So wanted some more insight on it.
> >> ~Jas
> >
> >   Where does it say so? Do you have a link?
>
> http://www.postgresql.org/docs/8.1/interactive/triggers.html
> 
> it says something like this:
> 
> " It is not currently possible to write a trigger function in the plain SQL
> function language. "
> 
> though lately I saw triggers written in pure sql in postgres

    Notice that the manual doesn't mention C, and I guess those "pure
    sql" triggers were written in PL/PgSQL, a "procedural language".

    As the following example fails to demonstrate, it's just SQL with a
    few control structures, very easy to get running if you have a bit
    of SQL and programming background.

    CREATE TABLE t (x SERIAL);

    CREATE FUNCTION sqlf()
    RETURNS SETOF t
    STABLE
    LANGUAGE SQL
    AS
    $$
        SELECT * FROM t;
    $$;

    CREATE FUNCTION plpgsqlf()
    RETURNS SETOF t
    STABLE
    LANGUAGE PLPGSQL
    AS
    $$
        DECLARE
            r t;
        BEGIN
            FOR r IN SELECT * FROM t LOOP
                RETURN NEXT r;
            END LOOP;
        END;
    $$;

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991