Re: Triggers in Postgres

# jsbali(at)gmail(dot)com / 2006-08-01 02:35:48 -0400:
> On 8/1/06, Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> wrote:
> >
> ># jsbali(at)gmail(dot)com / 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