triggers

"Kevin HaleBoyes" <kcboyes@yahoo.com> wrote in message
news:3BC30E19.1030203@yahoo.com...

I'm currently running a database on Oracle and would like to make my
application portable to the PostgreSQL database. Converting the tables
(specifically the attribute types) is fairly easy but I've run into a
problem with database triggers.

My application is designed so that insertions/updates to certain tables
only provide a limited number of attributes and an Oracle trigger fills
in the other attributes. For example, one table may have id and name
attributes but when the application inserts new records it only provides
the name value and a trigger fills in the id value (from a sequence

number).

choose a serial datatype and a sequence will be created and the default
value set.
have a look at the docs for nextval, currval and setval.

AFAIK triggers can be implemented as functions and writtenin a few different
languages but support needs to be compiled into postgresql.

Kevin HaleBoyes wrote:

...

I started looking at the trigger support in PostgreSQL and found what I
needed except I'm not sure how to write the "body" of the trigger. In
Oracle I write PL/SQL but it seems I may have to write C code on the
PostgreSQL side. Is this true? Is there a PostgreSQL procedural language
that I can write the body in?

Sure, PL/pgSQL. Check out:

http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html

for details.

Thanks,
Kevin.

Hope that helps,

Mike Mascari
mascarm@mascari.com

I started looking at the trigger support in PostgreSQL and found what I
needed except I'm not sure how to write the "body" of the trigger. In
Oracle I write PL/SQL but it seems I may have to write C code on the
PostgreSQL side. Is this true? Is there a PostgreSQL procedural language
that I can write the body in?

Why? What's wrong with plpgsql?

--thalis

As Steve Brett said, you need PL/pgSQL specifically made available, as there is no scripting supported initially (for securtity reasons, i'm told), but it isn't too hard.

you merely need to use the createlang in pgsql/bin, read the manual and / or email me for the lengthy explanation..

i believe PL/pgSQL is quite similar to PL/SQL, otherwise i can recommend the book

"beginning databases with postgresql" by Richard Stones and Neil Matthew, they're pretty thorough..

*********** REPLY SEPARATOR ***********

On 10-10-2001 at 12:33 Steve Brett wrote:

"Kevin HaleBoyes" <kcboyes@yahoo.com> wrote in message
news:3BC30E19.1030203@yahoo.com...

I'm currently running a database on Oracle and would like to make my
application portable to the PostgreSQL database. Converting the tables
(specifically the attribute types) is fairly easy but I've run into a
problem with database triggers.

My application is designed so that insertions/updates to certain tables
only provide a limited number of attributes and an Oracle trigger fills
in the other attributes. For example, one table may have id and name
attributes but when the application inserts new records it only provides
the name value and a trigger fills in the id value (from a sequence

number).

choose a serial datatype and a sequence will be created and the default
value set.
have a look at the docs for nextval, currval and setval.

AFAIK triggers can be implemented as functions and writtenin a few
different
languages but support needs to be compiled into postgresql.

I started looking at the trigger support in PostgreSQL and found what I
needed except I'm not sure how to write the "body" of the trigger. In
Oracle I write PL/SQL but it seems I may have to write C code on the
PostgreSQL side. Is this true? Is there a PostgreSQL procedural

language

that I can write the body in?

Thanks,
Kevin.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Mvh,

Johnny J���rgensen

johnny@halfahead.dk
+45 6315 7328