From ken@sunward.org Fri Nov 25 18:01:52 2005 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 8F45DDBB60 for ; Fri, 25 Nov 2005 14:01:52 -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 47862-05 for ; Fri, 25 Nov 2005 18:01:50 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from anu.ie (mail.anu.net [213.247.49.236]) by svr1.postgresql.org (Postfix) with ESMTP id 7F18ADAFA5 for ; Fri, 25 Nov 2005 14:01:47 -0400 (AST) Received: from kenxp (68.40.184.254) by anu.ie with ESMTP (Eudora Internet Mail Server X 3.2.8b1) for ; Fri, 25 Nov 2005 18:01:46 +0000 From: "Ken Winter" To: "'PostgreSQL pg-sql list'" Subject: Variable column names in PL/pgSQL RECORD referencces Date: Fri, 25 Nov 2005 13:01:40 -0500 Message-ID: <004901c5f1ea$4a322a10$6603a8c0@kenxp> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004A_01C5F1C0.615C2210" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.337 required=5 tests=[AWL=0.336, HTML_MESSAGE=0.001] X-Spam-Score: 0.337 X-Spam-Level: X-Archive-Number: 200511/244 X-Sequence-Number: 23495 This is a multi-part message in MIME format. ------=_NextPart_000_004A_01C5F1C0.615C2210 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable (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.) =20 In PL/pgSQL, is there a way to put a *variable* column-name in a dot notation reference to a RECORD column? =20 For example, suppose I want to write a function like the following, = which is to be called by a "BEFORE INSERT" trigger: =20 CREATE OR REPLACE FUNCTION foo ( ) RETURNS TRIGGER AS=20 ' DECLARE=20 var VARCHAR; BEGIN var :=3D TG_ARGV[0] =20 NEW. :=3D = ''whatever''; RETURN NEW; =20 END; ' LANGUAGE 'plpgsql' ; =20 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. =20 =20 What I don't know is what to put into the dot notation in place of = "." 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? =20 If not, can anybody suggest a way to write a trigger-called function = that would accomplish the same result? =20 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). =20 ~ TIA ~ Ken =20 =20 ------=_NextPart_000_004A_01C5F1C0.615C2210 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

(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 :=3D TG_ARGV[0] =

NEW.<the column whose name is the value of var> :=3D = ''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

------=_NextPart_000_004A_01C5F1C0.615C2210-- From tgl@sss.pgh.pa.us Fri Nov 25 19:22:13 2005 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 6A662DBDEE for ; Fri, 25 Nov 2005 15:22:13 -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 73941-02 for ; Fri, 25 Nov 2005 19:22:10 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 109BADBDE0 for ; Fri, 25 Nov 2005 15:22:07 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jAPJM8gw024859; Fri, 25 Nov 2005 14:22:08 -0500 (EST) To: "Ken Winter" cc: "'PostgreSQL pg-sql list'" Subject: Re: Variable column names in PL/pgSQL RECORD referencces In-reply-to: <004901c5f1ea$4a322a10$6603a8c0@kenxp> References: <004901c5f1ea$4a322a10$6603a8c0@kenxp> Comments: In-reply-to "Ken Winter" message dated "Fri, 25 Nov 2005 13:01:40 -0500" Date: Fri, 25 Nov 2005 14:22:08 -0500 Message-ID: <24858.1132946528@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.004 required=5 tests=[AWL=0.004] X-Spam-Score: 0.004 X-Spam-Level: X-Archive-Number: 200511/245 X-Sequence-Number: 23496 "Ken Winter" writes: > In PL/pgSQL, is there a way to put a *variable* column-name in a dot > notation reference to a RECORD column? No. Some of the other, less-strongly-typed PLs can do it. regards, tom lane