From ilan@iweb.com Wed Jun 20 16:44:31 2001 Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5KGiVa93161 for ; Wed, 20 Jun 2001 12:44:31 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from exchange.iweb.co.il ([212.68.138.186]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5IEYBa99079 for ; Mon, 18 Jun 2001 10:34:12 -0400 (EDT) (envelope-from ilan@iweb.com) Received: by EXCHANGE with Internet Mail Service (5.5.2653.19) id ; Mon, 18 Jun 2001 17:35:43 +0300 Message-ID: From: Ilan Fait To: "'pgsql-sql@postgresql.org'" Subject: How to build a TRIGGER in POSTGERSQL Date: Mon, 18 Jun 2001 17:35:42 +0300 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C0F803.F40BAC90" X-Archive-Number: 200106/319 X-Sequence-Number: 3600 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0F803.F40BAC90 Content-Type: text/plain; charset="windows-1255" Hi , I need some help in building trigger and information about sysdate (System date), any help will be appreciate. 1) I need to build a trigger that every time I insert into the table one of the columns will get +1 number. I have it in ORACLE ( see below the create of the sequence and the trigger) but how you can do it in PostGer SQL) CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 999999 CYCLE; CREATE TRIGGER AD_MNG_SYS_TRIG BEFORE INSERT ON AD_MNG_SYS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID FROM Dual; END; / 2) what is equal to 'sysdate' (to get/put the system date in a table) in PostGerSQL. Thanks, Ilan ------_=_NextPart_001_01C0F803.F40BAC90 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable How to build a TRIGGER in POSTGERSQL

       Hi ,<= /FONT>

      I need some= help in  building  trigger and information about sysdate (System= date), any help will be appreciate. 

      1) I need t= o build a trigger that every time I insert into the table  one of the = columns will  get +1 number.

       =     I have it in ORACLE ( see below the create of the sequen= ce and the trigger) but how you can do it in PostGer SQL)

 

   CREATE SEQUENCE AD_MNG_SYS_SE= Q MINVALUE 1 MAXVALUE 999999 CYCLE;

    CREATE TRIGGER AD_MNG_S= YS_TRIG
    BEFORE INSERT ON AD_MN= G_SYS
   REFERENCING OLD AS OLD NEW A= S NEW FOR EACH ROW
   BEGIN
   Select AD_MNG_SYS_SEQ.NEXTVA= L INTO :new.AMS_ID FROM Dual;
  END;
   /


       = 2) what is  equal to 'sysdate' (to get/put the system date in a table= )  in PostGerSQL.



     Thanks,

     Ilan

------_=_NextPart_001_01C0F803.F40BAC90-- From ilan@iweb.com Mon Jun 18 15:01:19 2001 Received: from exchange.iweb.co.il ([212.68.138.186]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5IF1Ia13250 for ; Mon, 18 Jun 2001 11:01:19 -0400 (EDT) (envelope-from ilan@iweb.com) Received: by EXCHANGE with Internet Mail Service (5.5.2653.19) id ; Mon, 18 Jun 2001 18:02:54 +0300 Message-ID: From: Ilan Fait To: "'pgsql-sql@postgresql.org'" Subject: How to build a TRIGGER in POSTGERSQL Date: Mon, 18 Jun 2001 18:02:54 +0300 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C0F807.C0814250" X-Archive-Number: 200106/255 X-Sequence-Number: 3513 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0F807.C0814250 Content-Type: text/plain; charset="windows-1255" Hi , I need some help in building trigger and information about sysdate (System date), any help will be appreciate. 1) I need to build a trigger that every time I insert into the table one of the columns will get +1 number. I have it in ORACLE ( see below the create of the sequence and the trigger) but how you can do it in PostGer SQL) CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 999999 CYCLE; CREATE TRIGGER AD_MNG_SYS_TRIG BEFORE INSERT ON AD_MNG_SYS REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID FROM Dual; END; / 2) what is equal to 'sysdate' (to get/put the system date in a table) in PostGerSQL. Thanks, Ilan ------_=_NextPart_001_01C0F807.C0814250 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable How to build a TRIGGER in POSTGERSQL

       Hi ,<= /FONT>

      I need some= help in  building  trigger and information about sysdate (System= date), any help will be appreciate. 

      1) I need t= o build a trigger that every time I insert into the table  one of the = columns will  get +1 number.

       =     I have it in ORACLE ( see below the create of the sequen= ce and the trigger) but how you can do it in PostGer SQL)

 

   CREATE SEQUENCE AD_MNG_SYS_SE= Q MINVALUE 1 MAXVALUE 999999 CYCLE;

    CREATE TRIGGER AD_MNG_S= YS_TRIG
    BEFORE INSERT ON AD_MN= G_SYS
   REFERENCING OLD AS OLD NEW A= S NEW FOR EACH ROW
   BEGIN
   Select AD_MNG_SYS_SEQ.NEXTVA= L INTO :new.AMS_ID FROM Dual;
  END;
   /


       = 2) what is  equal to 'sysdate' (to get/put the system date in a table= )  in PostGerSQL.



     Thanks,

     Ilan

------_=_NextPart_001_01C0F807.C0814250-- From alla@sergey.com Tue Jun 19 13:21:36 2001 Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5JDLaa74401 for ; Tue, 19 Jun 2001 09:21:36 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5IL1Sa87166 for ; Mon, 18 Jun 2001 17:01:28 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f5IKrKa70835 for pgsql-sql@postgresql.org; Mon, 18 Jun 2001 16:53:20 -0400 (EDT) (envelope-from news) From: alla@sergey.com (Alla) X-Newsgroups: comp.databases.postgresql.sql Subject: Re: How to build a TRIGGER in POSTGERSQL Date: 18 Jun 2001 13:53:16 -0700 Organization: http://groups.google.com/ Lines: 34 Message-ID: <9275d56e.0106181253.27beed20@posting.google.com> References: Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: groups-support@google.com To: pgsql-sql@postgresql.org X-Archive-Number: 200106/289 X-Sequence-Number: 3552 > CREATE SEQUENCE AD_MNG_SYS_SEQ MINVALUE 1 MAXVALUE 999999 CYCLE; Stays pretty much the same > > CREATE TRIGGER AD_MNG_SYS_TRIG > BEFORE INSERT ON AD_MNG_SYS > REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW > BEGIN > Select AD_MNG_SYS_SEQ.NEXTVAL INTO :new.AMS_ID FROM Dual; > END; > / create function ad_mng_sys_proc() returns opaque as ' begin new.ams_id := nextval(''ad_mng_sys_seq''); return new; end; ' LANGUAGE 'plpgsql'; create trigger ad_mng_sys_trig before insert on ad_mng_sys for each row execute procedure ad_mng_sys_proc(); > 2) what is equal to 'sysdate' (to get/put the system date in a > table) in PostGerSQL. now() Enjoy :-)) Alla Gribov From darcy@druid.net Tue Jun 19 11:43:13 2001 Received: from druid.net (druid.net [216.126.72.98]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5JBhDa33191 for ; Tue, 19 Jun 2001 07:43:13 -0400 (EDT) (envelope-from darcy@druid.net) Received: by druid.net (Postfix, from userid 1000) id 716CC1A78; Tue, 19 Jun 2001 07:43:08 -0400 (EDT) Subject: Re: How to build a TRIGGER in POSTGERSQL In-Reply-To: "from Ilan Fait at Jun 18, 2001 06:02:54 pm" To: Ilan Fait Date: Tue, 19 Jun 2001 07:43:08 -0400 (EDT) Cc: "'pgsql-sql@postgresql.org'" X-Mailer: ELM [version 2.4ME+ PL78 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII Message-Id: <20010619114308.716CC1A78@druid.net> From: darcy@druid.net (D'Arcy J.M. Cain) X-Archive-Number: 200106/285 X-Sequence-Number: 3548 Thus spake Ilan Fait > 1) I need to build a trigger that every time I insert into the table > one of the columns will get +1 number. The easiest way in PostgreSQL is to simply make the field a serial type. CREATE TABLE mytable ( id SERIAL PRIMARY KEY, myname TEXT); > 2) what is equal to 'sysdate' (to get/put the system date in a > table) in PostGerSQL. Again, the table definition can handle this. CREATE TABLE mytable ( id SERIAL PRIMARY KEY, mydate DATE DEFAULT CURRENT_DATE, myname TEXT); You should check out the docs on the web site. They are quite comprehensive. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.