X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7A20BDBBB4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 24 Nov 2005 09:57:04 -0400 (AST)
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 17880-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 24 Nov 2005 13:57:07 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25])
	by svr1.postgresql.org (Postfix) with ESMTP id D27FADBB53
	for <pgsql-sql@postgresql.org>; Thu, 24 Nov 2005 09:57:01 -0400 (AST)
Received: from cu-chulhainn.bray-healthcare.com (mailgate.bray-healthcare.com
	[80.177.250.202])
	by svr2.postgresql.org (Postfix) with ESMTP id BD367F0B16
	for <pgsql-sql@postgresql.org>; Thu, 24 Nov 2005 13:57:05 +0000 (GMT)
Received: from braydb.bray-healthcare.com ([192.168.1.18])
	by cu-chulhainn.bray-healthcare.com with esmtp (Exim 4.50)
	id 1EfHax-0004wa-H1; Thu, 24 Nov 2005 13:57:03 +0000
Subject: Re: 
From: Oliver Elphick <olly@lfix.co.uk>
Reply-To: olly@lfix.co.uk
To: Ken Winter <ken@sunward.org>
Cc: PostgreSQL pg-sql list <pgsql-sql@postgresql.org>
In-Reply-To: <004e01c5f0ae$c4d4bfe0$6503a8c0@kenxp>
References: <004e01c5f0ae$c4d4bfe0$6503a8c0@kenxp>
Content-Type: text/plain
Date: Thu, 24 Nov 2005 13:57:03 +0000
Message-Id: <1132840623.28235.31.camel@braydb.bray-healthcare.com>
Mime-Version: 1.0
X-Mailer: Evolution 2.2.3 
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/231
X-Sequence-Number: 23482

On Wed, 2005-11-23 at 23:23 -0500, Ken Winter wrote:
> In PL/pgSQL, is there a way to put a *variable* column-name in a dot
> notation reference to a RECORD column?
> 
> For example, suppose I want to write a function like the following, which is
> to be called by a "BEFORE INSERT" trigger:
> 
> CREATE OR REPLACE FUNCTION foo (  ) RETURNS TRIGGER AS 
>     '
>     DECLARE 
>         var VARCHAR;
>     BEGIN
>         var := TG_ARGV[0]       
>         NEW.<the column whose name is the value of var> := ''whatever'';
>         RETURN NEW;     
>     END;
>     '
>     LANGUAGE 'plpgsql'
> ;
> 
> The aim of this uninteresting function is to assign the value 'whatever' to
> the table column that is passed in by the calling trigger as TG_ARGV[0],
> i.e. the first calling argument.  
> 
> What I don't know is what to put into the dot notation in place of ".<the
> column whose name is the value of var>" so that the column of NEW that is
> addressed by the assignment statement is the one passed in as the first
> argument.  Is there any PL/pgSQL construct that could be substituted in here
> to achieve this result?

Unfortunately not.

> If not, can anybody suggest a way to write a trigger-called function that
> would accomplish the same result?

You would have to do something like:

CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS 
    $$
    DECLARE 
        var VARCHAR;
    BEGIN
        var := TG_ARGV[0];
        IF var = 'column_1' THEN
            NEW.column_1 = 'whatever';
        ELSIF var = 'column_2' THEN
            NEW.column_2 = 'whatever';
        ...
        END IF;
        RETURN NEW;
   END;
   $$
   LANGUAGE plpgsql;

Oliver Elphick