pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

(no subject)
3+ messages / 3 participants
[nested] [flat]

* (no subject)
@ 2005-11-24 04:23  Ken Winter <ken@sunward.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Ken Winter @ 2005-11-24 04:23 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re:
@ 2005-11-24 13:57  Oliver Elphick <olly@lfix.co.uk>
  parent: Ken Winter <ken@sunward.org>
  0 siblings, 1 reply; 3+ messages in thread

From: Oliver Elphick @ 2005-11-24 13:57 UTC (permalink / raw)
  To: Ken Winter <ken@sunward.org>; +Cc: 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re:
@ 2005-11-24 15:18  Achilleus Mantzios <achill@matrix.gatewaynet.com>
  parent: Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Achilleus Mantzios @ 2005-11-24 15:18 UTC (permalink / raw)
  To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Ken Winter <ken@sunward.org>; pgsql-sql

O Oliver Elphick Ýãñáøå óôéò Nov 24, 2005 :

> 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?

You can do it with a C function.
(See dbmirror's pending.c for a reference)

> 

-- 
-Achilleus




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2005-11-24 15:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-24 04:23  Ken Winter <ken@sunward.org>
2005-11-24 13:57 ` Re: Oliver Elphick <olly@lfix.co.uk>
2005-11-24 15:18   ` Re: Achilleus Mantzios <achill@matrix.gatewaynet.com>

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