From: Oliver Elphick <olly@lfix.co.uk>
To: Ken Winter <ken@sunward.org>
Cc: PostgreSQL pg-sql list <pgsql-sql@postgresql.org>
Subject: Re:
Date: Thu, 24 Nov 2005 13:57:03 +0000
Message-ID: <1132840623.28235.31.camel@braydb.bray-healthcare.com> (raw)
In-Reply-To: <004e01c5f0ae$c4d4bfe0$6503a8c0@kenxp>
References: <004e01c5f0ae$c4d4bfe0$6503a8c0@kenxp>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, ken@sunward.org
Subject: Re:
In-Reply-To: <1132840623.28235.31.camel@braydb.bray-healthcare.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox