Thread: How to dynamically call a column in plpgsql

Hi!

I am trying to fetch a value from a column, where the
columnname is a value in another column.

See below ..

table constraint_1 (
...
cons_col VARCHAR(15) NOT NULL,
...
)


Then in the trigger (written in plpgsql) I want to
fetch the appropriate column from the NEW record.
--- trigger --
bla bla bla...

select cons_col from constraint_1 where bla = bla ...
-- the_val := NEW.cons_col;

bla. bla .bla.
return NEW;
bla bla bla..
----

Well I have tried a couple different things .. none
seem to work. I know that this is possible if the
trigger is written in C .. but it might be nice to
know how to do this in plpgsql.
Anyone out there that knows how to do this?



_____________________________________________________
Gratis e-mail resten av livet på www.yahoo.se/mail
Busenkelt!

Sigurdur Helgason wrote:
> Hi!
>
> I am trying to fetch a value from a column, where the
> columnname is a value in another column.
>
> See below ..
>
> table constraint_1 (
> ...
> cons_col VARCHAR(15) NOT NULL,
> ...
> )
>
>
> Then in the trigger (written in plpgsql) I want to
> fetch the appropriate column from the NEW record.
> --- trigger --
> bla bla bla...
>
> select cons_col from constraint_1 where bla = bla ...
> -- the_val := NEW.cons_col;
>
> bla. bla .bla.
> return NEW;
> bla bla bla..
> ----
>
> Well I have tried a couple different things .. none
> seem to work. I know that this is possible if the
> trigger is written in C .. but it might be nice to
> know how to do this in plpgsql.
> Anyone out there that knows how to do this?
>
>
I think something like the following should do:
SELECT INTO cl_nm cons_col FROM constrait_1 WHERE ..........
EXECUTE ''SELECT * FROM tbl WHERE '' || quote_ident(cl_nm) || ''=.......'';
the plpgsql docs contain more docs for EXECUTE (such as when to use quote_ident)
hth,
- Stuart

 --- "Henshall, Stuart - Design & Print"
<SHenshall@westcountry-design-print.co.uk> skrev: >
[previous stuff cut]
> I think something like the following should do:
> SELECT INTO cl_nm cons_col FROM constrait_1 WHERE
> ..........
> EXECUTE ''SELECT * FROM tbl WHERE '' ||
> quote_ident(cl_nm) || ''=.......'';
> the plpgsql docs contain more docs for EXECUTE (such
> as when to use
> quote_ident)
> hth,
> - Stuart
>
Thanks Stuart, but either I am very tired today, or
you must have misunderstood me or my original question
was poorly phraised. Since I fail to see how your
answer helps me to access the 'cons_col' value in the
NEW record?

--
Say for example that NEW contains 3 columns: val1,
val2, val3

In contraints table the value of cons_col culumn
contains a string (either val1, val2 or val3).

if cons_col == "val1" I would like to read the
NEW.val1 value into a variable.

Regards
/Siggi


_____________________________________________________
Gratis e-mail resten av livet på www.yahoo.se/mail
Busenkelt!