Triggers in Postgres

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

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

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

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

~jas

# 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

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.

# 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

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