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