From: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | Ken Winter <ken(at)sunward(dot)org> |
Cc: | PostgreSQL pg-sql list <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: |
Date: | 2005-11-24 13:57:03 |
Message-ID: | 1132840623.28235.31.camel@braydb.bray-healthcare.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleus Mantzios | 2005-11-24 15:18:23 | Re: |
Previous Message | Leif B. Kristensen | 2005-11-24 11:16:00 | Index wonder |