Thread: triggers
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). 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.
"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. >
> 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 securtityreasons, 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
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