From ffo2lp@yahoo.com Mon Oct 20 13:01:30 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E2E94D1B51E for ; Mon, 20 Oct 2003 13:01:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 14944-09 for ; Mon, 20 Oct 2003 10:01:03 -0300 (ADT) Received: from web60210.mail.yahoo.com (web60210.mail.yahoo.com [216.109.118.105]) by svr1.postgresql.org (Postfix) with SMTP id 888ABD1B501 for ; Mon, 20 Oct 2003 10:00:59 -0300 (ADT) Message-ID: <20031020130058.61589.qmail@web60210.mail.yahoo.com> Received: from [24.75.42.210] by web60210.mail.yahoo.com via HTTP; Mon, 20 Oct 2003 06:00:58 PDT Date: Mon, 20 Oct 2003 06:00:58 -0700 (PDT) From: Dmitri Fuerle Subject: Question regarding triggers To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-902308228-1066654858=:61044" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.5 tagged_above=0.0 required=5.0 tests=HTML_20_30 X-Spam-Level: * X-Archive-Number: 200310/232 X-Sequence-Number: 15632 --0-902308228-1066654858=:61044 Content-Type: text/plain; charset=us-ascii I'm writing a trigger but running into problems. My problem is that I can not determine anyway to tell what fields are in the *new* record. Without knowing what fields are there I get runtime errors if that's not what is being updated example: CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' begin If new.shipment_type_id = 4 then --do something end if; return new; end; in the above example everything works if shipment_type_id is being updated. If it is not I will receive a run-time error because shipment_type_id is not part of the record new. Please help, Dmitri --------------------------------- Do you Yahoo!? The New Yahoo! Shopping - with improved product search --0-902308228-1066654858=:61044 Content-Type: text/html; charset=us-ascii
 
   I'm writing a trigger but running into problems.  My problem is that I can not determine anyway to tell what fields are in the *new* record.  Without knowing what fields are there I get runtime errors if that's not what is being updated example:
 
CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS'
begin
    If new.shipment_type_id = 4 then
        --do something
    end if;
    return new;
end;
 
in the above example everything works if shipment_type_id is being updated.  If it is not I will receive a run-time error because shipment_type_id is not part of the record new.
 
Please help,
Dmitri
 

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search --0-902308228-1066654858=:61044-- From achill@matrix.gatewaynet.com Mon Oct 20 13:09:19 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0700DD1B52D for ; Mon, 20 Oct 2003 13:09:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 16799-09 for ; Mon, 20 Oct 2003 10:08:51 -0300 (ADT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id D74FCD1B53D for ; Mon, 20 Oct 2003 10:08:45 -0300 (ADT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id h9KDMddN029920; Mon, 20 Oct 2003 16:22:39 +0300 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id h9KDMdYK029916; Mon, 20 Oct 2003 16:22:39 +0300 Date: Mon, 20 Oct 2003 16:22:39 +0300 (EEST) From: achill@matrix.gatewaynet.com To: Dmitri Fuerle Cc: pgsql-sql@postgresql.org Subject: Re: Question regarding triggers In-Reply-To: <20031020130058.61589.qmail@web60210.mail.yahoo.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/233 X-Sequence-Number: 15633 Why dont you try to write your trigger in C? On Mon, 20 Oct 2003, Dmitri Fuerle wrote: > > I'm writing a trigger but running into problems. My problem is that I can not determine anyway to tell what fields are in the *new* record. Without knowing what fields are there I get runtime errors if that's not what is being updated example: > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > begin > If new.shipment_type_id = 4 then > --do something > end if; > return new; > end; > > in the above example everything works if shipment_type_id is being updated. If it is not I will receive a run-time error because shipment_type_id is not part of the record new. > > Please help, > Dmitri > > > > > --------------------------------- > Do you Yahoo!? > The New Yahoo! Shopping - with improved product search -- -Achilleus From bhuvan@symonds.net Mon Oct 20 13:23:19 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 26D06D1B4FB for ; Mon, 20 Oct 2003 13:23:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 24781-05 for ; Mon, 20 Oct 2003 10:22:51 -0300 (ADT) Received: from Gateway.bksys.co.in (unknown [61.3.64.14]) by svr1.postgresql.org (Postfix) with ESMTP id 11824D1B544 for ; Mon, 20 Oct 2003 10:22:46 -0300 (ADT) Received: from bksys.co.in (mail.bksys.co.in [192.168.1.1]) by Gateway.bksys.co.in (8.11.6/8.11.6) with ESMTP id h9KDMjp12667; Mon, 20 Oct 2003 18:52:47 +0530 Received: from Bhuvan.bksys.co.in (Bhuvan.bksys.co.in [192.168.1.71]) by bksys.co.in (8.11.6/8.8.5) with ESMTP id h9KDMjO12433; Mon, 20 Oct 2003 18:52:45 +0530 Date: Mon, 20 Oct 2003 19:01:43 +0530 (IST) From: "A.Bhuvaneswaran" X-X-Sender: bhuvan@Bhuvan.bksys.co.in To: achill@matrix.gatewaynet.com Cc: Dmitri Fuerle , Subject: Re: Question regarding triggers In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/234 X-Sequence-Number: 15634 > Why dont you try to write your trigger in C? Hi, one cannot write triggered procedures in C. Currently, it can only be written in plpsgql. > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > > begin > > If new.shipment_type_id = 4 then > > --do something > > end if; > > return new; > > end; > > in the above example everything works if shipment_type_id is being > > updated. If it is not I will receive a run-time error because > > shipment_type_id is not part of the record new. All the fields of updated record must available in NEW variable. Refer the manual for details. Forward your sql & run-time error for further assistance. regards, bhuvaneswaran From tgl@sss.pgh.pa.us Mon Oct 20 13:49:19 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 92625D1B4E1 for ; Mon, 20 Oct 2003 13:49:19 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 25869-10 for ; Mon, 20 Oct 2003 10:48:52 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 2CE88D1B4EF for ; Mon, 20 Oct 2003 10:48:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h9KDmqit002249; Mon, 20 Oct 2003 09:48:52 -0400 (EDT) To: "A.Bhuvaneswaran" Cc: achill@matrix.gatewaynet.com, Dmitri Fuerle , pgsql-sql@postgresql.org Subject: Re: Question regarding triggers In-reply-to: References: Comments: In-reply-to "A.Bhuvaneswaran" message dated "Mon, 20 Oct 2003 19:01:43 +0530" Date: Mon, 20 Oct 2003 09:48:52 -0400 Message-ID: <2248.1066657732@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/236 X-Sequence-Number: 15636 "A.Bhuvaneswaran" writes: >> Why dont you try to write your trigger in C? > Hi, one cannot write triggered procedures in C. Oh? All the built-in trigger procedures are. regards, tom lane From achill@matrix.gatewaynet.com Mon Oct 20 13:42:51 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 08FFCD1B545 for ; Mon, 20 Oct 2003 13:42:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 25585-10 for ; Mon, 20 Oct 2003 10:42:23 -0300 (ADT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by svr1.postgresql.org (Postfix) with ESMTP id 24A68D1B4FB for ; Mon, 20 Oct 2003 10:42:18 -0300 (ADT) Received: from matrix.gatewaynet.com (localhost.localdomain [127.0.0.1]) by matrix.gatewaynet.com (8.12.8/8.12.8) with ESMTP id h9KDuAdN030048; Mon, 20 Oct 2003 16:56:10 +0300 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.8/8.12.8/Submit) with ESMTP id h9KDuASs030044; Mon, 20 Oct 2003 16:56:10 +0300 Date: Mon, 20 Oct 2003 16:56:10 +0300 (EEST) From: achill@matrix.gatewaynet.com To: "A.Bhuvaneswaran" Cc: Dmitri Fuerle , Subject: Re: Question regarding triggers In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/235 X-Sequence-Number: 15635 On Mon, 20 Oct 2003, A.Bhuvaneswaran wrote: > > Why dont you try to write your trigger in C? > > Hi, one cannot write triggered procedures in C. Currently, it can only be > written in plpsgql. Where did you get that impression from? Do an SELECT tgrelid,tgfoid,proname from pg_trigger,pg_proc where tgfoid=pg_proc.oid and prolang=13; in your system to check if you have any :) > > > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > > > begin > > > If new.shipment_type_id = 4 then > > > --do something > > > end if; > > > return new; > > > end; > > > in the above example everything works if shipment_type_id is being > > > updated. If it is not I will receive a run-time error because > > > shipment_type_id is not part of the record new. > > All the fields of updated record must available in NEW variable. Refer > the manual for details. Forward your sql & run-time error for further > assistance. > > regards, > bhuvaneswaran > > -- -Achilleus From ffo2lp@yahoo.com Mon Oct 20 15:07:51 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2766DD1B4E3 for ; Mon, 20 Oct 2003 15:07:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39159-02 for ; Mon, 20 Oct 2003 12:07:20 -0300 (ADT) Received: from web60205.mail.yahoo.com (web60205.mail.yahoo.com [216.109.118.100]) by svr1.postgresql.org (Postfix) with SMTP id 9F4EDD1B501 for ; Mon, 20 Oct 2003 12:07:19 -0300 (ADT) Message-ID: <20031020150713.89414.qmail@web60205.mail.yahoo.com> Received: from [24.75.42.210] by web60205.mail.yahoo.com via HTTP; Mon, 20 Oct 2003 08:07:13 PDT Date: Mon, 20 Oct 2003 08:07:13 -0700 (PDT) From: Dmitri Fuerle Subject: Re: Question regarding triggers To: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-523230983-1066662433=:89132" X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=EMAIL_ATTRIBUTION, HTML_20_30, IN_REP_TO, QUOTED_EMAIL_TEXT, REPLY_WITH_QUOTES X-Spam-Level: X-Archive-Number: 200310/237 X-Sequence-Number: 15637 --0-523230983-1066662433=:89132 Content-Type: text/plain; charset=us-ascii Thanks for the help. I discovered my error while coming up with a better example. Thanks, Dmitri "A.Bhuvaneswaran" wrote: > Why dont you try to write your trigger in C? Hi, one cannot write triggered procedures in C. Currently, it can only be written in plpsgql. > > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS' > > begin > > If new.shipment_type_id = 4 then > > --do something > > end if; > > return new; > > end; > > in the above example everything works if shipment_type_id is being > > updated. If it is not I will receive a run-time error because > > shipment_type_id is not part of the record new. All the fields of updated record must available in NEW variable. Refer the manual for details. Forward your sql & run-time error for further assistance. regards, bhuvaneswaran --------------------------------- Do you Yahoo!? The New Yahoo! Shopping - with improved product search --0-523230983-1066662433=:89132 Content-Type: text/html; charset=us-ascii
  Thanks for the help.  I discovered my error while coming up with a better example.
 
 
Thanks,
  Dmitri


"A.Bhuvaneswaran" <bhuvan@symonds.net> wrote:
> Why dont you try to write your trigger in C?

Hi, one cannot write triggered procedures in C. Currently, it can only be
written in plpsgql.

> > CREATE FUNCTION "public"."check_shipment" () RETURNS trigger AS'
> > begin
> > If new.shipment_type_id = 4 then
> > --do something
> > end if;
> > return new;
> > end;
> > in the above example everything works if shipment_type_id is being
> > updated. If it is not I will receive a run-time error because
> > shipment_type_id is not part of the record new.

All the fields of updated record must available in NEW variable. Refer
the manual for details. Forward your sql & run-time error for further
assistance.

regards,
bhuvaneswaran

Do you Yahoo!?
The New Yahoo! Shopping - with improved product search --0-523230983-1066662433=:89132--