Variable column names in PL/pgSQL RECORD referencces

From: "Ken Winter" <ken(at)sunward(dot)org>
To: "'PostgreSQL pg-sql list'" <pgsql-sql(at)postgresql(dot)org>
Subject: Variable column names in PL/pgSQL RECORD referencces
Date: 2005-11-25 18:01:40
Message-ID: 004901c5f1ea$4a322a10$6603a8c0@kenxp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

(Sorry for the redundancy - I sent this query earlier but forgot to put a
title on it. Seems like it would be more useful with a title, so here it is
again. If there's a moderator who can delete my earlier message, please do
so.)

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?

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

In case it's not obvious, the underlying goal is to write a single
trigger-called function that could modify different columns for each trigger
that called it, where each trigger specified the target column by a calling
argument (or by any other viable mechanism).

~ TIA

~ Ken

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2005-11-25 19:22:08 Re: Variable column names in PL/pgSQL RECORD referencces
Previous Message David Pradier 2005-11-25 16:52:59 Order of precedence between AND and OR.