Thread: Triggers in Postgres

Hi,
Was wondering if one can write triggers with SQL statements as we have in other RDBMS like SQL Server and oracle.
Can these be written in procedural languages only?
Please put some insight on what needs to be known before working with triggers in postgres.
I've already read the postgres manual in postgres official website.

Regards,
Jas

Jasbinder Bali wrote:
> Hi,
> Was wondering if one can write triggers with SQL statements as we have
> in other RDBMS like SQL Server and oracle.

What would such a trigger "in SQL statements" look like?
SQL Server has Triggers in Transact-SQL, which is just something
like a pl/language.

> Can these be written in procedural languages only?
> Please put some insight on what needs to be known before working with
> triggers in postgres.

You should probably try it when you read all manuals :-)
pl/sql is very close to pure SQL, so if you manage to write
your trigger with it - why not? :-)

What should your trigger do btw?

Regards
Tino

Jasbinder Bali wrote:
> 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.

See chapters 35 - 39 of the manual for details. In particular a
discussion of plpgsql and an example of writing a trigger using it.

http://www.postgresql.org/docs/8.1/static/plpgsql.html

There are other procedural languages available too: php, ruby, shell, R,
all of various levels of maturity and with different features.
--
   Richard Huxton
   Archonet Ltd

# jsbali@gmail.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?

--
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

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

> 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. "

The whole paragraph says.

"It is also possible to write a trigger function in C, although most
people find it easier to use one of the procedural languages. It is not
currently possible to write a trigger function in the plain SQL function
language."

That is: you can and you should write your trigger in a procedural
language. In particular - if you want to stay as closed as possible
to SQL you should use procedural SQL, which in PostgreSQL is called
PL/pgSQL:
http://www.postgresql.org/docs/8.1/interactive/plpgsql.html

Bye, Chris.

Chris Mair <list@1006.org> writes:
>> http://www.postgresql.org/docs/8.1/interactive/triggers.html
>> " It is not currently possible to write a trigger function in the
>> plain SQL function language. "

> The whole paragraph says.

> "It is also possible to write a trigger function in C, although most
> people find it easier to use one of the procedural languages. It is not
> currently possible to write a trigger function in the plain SQL function
> language."

And that comes *after* a paragraph talking about the different
procedural languages you can write a trigger in.  I can't imagine how
anyone would come away from reading that with the impression that C
is the first recommendation for writing triggers.

            regards, tom lane