From Bart.Degryse@indicator.be Thu Jun  2 11:57:43 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5DB8E52874
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Jun 2005 08:57:43 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 72983-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu,  2 Jun 2005 11:57:39 +0000 (GMT)
Received: from interscan.indicator.com (mail.indicator.be [193.121.199.3])
	by svr1.postgresql.org (Postfix) with ESMTP id 288D952838
	for <pgsql-sql@postgresql.org>; Thu,  2 Jun 2005 08:57:37 -0300 (ADT)
Received: from DOMEXT-MTA by webaccess.indicator.bewith Novell_GroupWise;
	Thu, 02 Jun 2005 13:57:39 +0200
Message-Id: <s29f1053.094@webaccess.indicator.be>
X-Mailer: Novell GroupWise Internet Agent 6.5.3 
Date: Thu, 02 Jun 2005 13:57:26 +0200
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: <pgsql-sql@postgresql.org>
Subject: plpgsql and triggers
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__Part7655A736.0__="
X-imss-version: 2.025
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.137 tagged_above=0 required=5
	tests=FORGED_RCVD_HELO, HTML_40_50, HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200506/67
X-Sequence-Number: 21668

This is a MIME message. If you are reading this text, you may want to 
consider changing to a mail reader or gateway that understands how to 
properly handle MIME multipart messages.

--=__Part7655A736.0__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I'm looking for a way to use a parameter given to a trigger function as
fieldname. It should be something like
create function f_makeupper() returns trigger as '
begin
    NEW.TG_ARGV[0] := upper(NEW.TG_ARGV[0]);
    RETURN NEW;
end;
' language 'plpgsql';
create trigger "TRIG_tbltest" before insert on tbltest for each row
execute procedure f_makeupper("fieldname");
 
I can create both the function and the trigger, but on inserting a new
record I get an error telling me that "record 'new' has no field
'tg_argv'"
 
What am I doing wrong ?
 

--=__Part7655A736.0__=
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1"=
>
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt MS Sans Serif">
<DIV>I'm looking for a way to use a parameter given to a trigger function =
as fieldname. It should be something like</DIV>
<DIV>create function f_makeupper() returns trigger as '</DIV>
<DIV>begin</DIV>
<DIV>&nbsp;&nbsp;&nbsp; NEW.TG_ARGV[0] :=3D upper(NEW.TG_ARGV[0]);</DIV>
<DIV>&nbsp;&nbsp;&nbsp; RETURN NEW;</DIV>
<DIV>end;</DIV>
<DIV>' language 'plpgsql';</DIV>
<DIV>create trigger "TRIG_tbltest" before insert on tbltest for each row =
execute procedure f_makeupper("fieldname");</DIV>
<DIV>&nbsp;</DIV>
<DIV>I can create both the function and the trigger, but on inserting a =
new record I get an error telling me that "record 'new' has no field =
'tg_argv'"</DIV>
<DIV>&nbsp;</DIV>
<DIV>What am I doing wrong ?</DIV>
<DIV>&nbsp;</DIV></BODY></HTML>

--=__Part7655A736.0__=--



From mike@fuhr.org Mon Jun  6 02:30:59 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 324AD528BE
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun,  5 Jun 2005 23:30:59 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 39972-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  6 Jun 2005 02:30:54 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 7F90F528AA
	for <pgsql-sql@postgresql.org>; Sun,  5 Jun 2005 23:30:52 -0300 (ADT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j562UneM053890
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Sun, 5 Jun 2005 20:30:53 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j562Un6g092492;
	Sun, 5 Jun 2005 20:30:49 -0600 (MDT)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j562Umbg092491;
	Sun, 5 Jun 2005 20:30:48 -0600 (MDT) (envelope-from mfuhr)
Date: Sun, 5 Jun 2005 20:30:48 -0600
From: Michael Fuhr <mike@fuhr.org>
To: Bart Degryse <Bart.Degryse@indicator.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: plpgsql and triggers
Message-ID: <20050606023048.GA92417@winnie.fuhr.org>
References: <s29f1053.094@webaccess.indicator.be>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <s29f1053.094@webaccess.indicator.be>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200506/72
X-Sequence-Number: 21673

On Thu, Jun 02, 2005 at 01:57:26PM +0200, Bart Degryse wrote:
>
> I'm looking for a way to use a parameter given to a trigger function as
> fieldname. It should be something like
> create function f_makeupper() returns trigger as '
> begin
>     NEW.TG_ARGV[0] := upper(NEW.TG_ARGV[0]);
>     RETURN NEW;
> end;
> ' language 'plpgsql';

As you've discovered, this doesn't work in PL/pgSQL: the above code
references the new row's TG_ARGV column, which doesn't exist.  This
comes up frequently; see the list archives for past discussion.  The
usual advice is to use a language like PL/Perl, PL/Tcl, PL/Python, etc.
that provides this capability.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/