Thread: Variable as a variable name in PL/pgSQL

I'm trying to implement a sort of templates for a user database.

I tought of two possibilities. In the first I have a table like this:

CREATE TABLE templates
(
 tplname       varchar(32),
 fieldname     varchar(32),
 value         varchar(32)
);

Where I specify the value to be assigned to each field of the user's tuple.
I started writing a trigger to force the template values in the user's record
whenever it's updated or inserted. I'm using PL/pgSQL, and I'd like to continue
using it.

The function should look like the following:

CREATE FUNCTION users_trig_func() RETURNS opaque AS '
  BEGIN

    FOR row IN SELECT * FROM templates WHERE tplname = new.template;
      NEW."row.fieldname" := row.fieldvalue;
    END LOOP;

    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';


Obviously the problem is the >>>NEW."row.fieldname" := expr<<< statememt.
I dont know how (if possible at all) to assign a variabile whose name is
contained in another variable, something like eval() in PHP3.

Is there a syntax or a hack ?
Whould it be hard to implement it at language level ?

Thanks in advance!

Regards.

--
 Daniele

-------------------------------------------------------------------------------
We are using Linux daily to UP our productivity - so UP yours!
(Adapted from Pat Paulsen by Joe Sloan)
-------------------------------------------------------------------------------
 Dal 28-09-1998, con un solo POP abbiamo fatto guadagnare a telecom italia
 15.617.190 Lire solo in scatti alla risposta. Non male eh ?
-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------

>>>>> "DO" == Daniele Orlandi <daniele@orlandi.com> writes:

 DO> I'm trying to implement a sort of templates for a user database.

 DO> I tought of two possibilities. In the first I have a table like this:

 DO> CREATE TABLE templates
 DO> (
 DO> tplname       varchar(32),
 DO> fieldname     varchar(32),
 DO> value         varchar(32)
 DO> );

 DO> Where I specify the value to be assigned to each field of the user's tuple.
 DO> I started writing a trigger to force the template values in the user's record
 DO> whenever it's updated or inserted. I'm using PL/pgSQL, and I'd like to continue
 DO> using it.

 DO> The function should look like the following:

 DO> CREATE FUNCTION users_trig_func() RETURNS opaque AS '
 DO> BEGIN

 DO> FOR row IN SELECT * FROM templates WHERE tplname = new.template;
 DO> NEW."row.fieldname" := row.fieldvalue;
 DO> END LOOP;

 DO> RETURN NEW;
 DO> END;
 DO> ' LANGUAGE 'plpgsql';


 DO> Obviously the problem is the >>>NEW."row.fieldname" := expr<<< statememt.
 DO> I dont know how (if possible at all) to assign a variabile whose name is
 DO> contained in another variable, something like eval() in PHP3.

 DO> Is there a syntax or a hack ?
 DO> Whould it be hard to implement it at language level ?

Excuse my english :(

I think this is impossible in 'plpgsql', because it is pre-compilrd
language.

But there is, in my mind, no
problem write script on any 'usual' language, such as perl. This
script can take data from your table, and send nesessary SQL queries
to server.


--
Anatoly K. Lasareff              Email:       tolik@icomm.ru
Senior programmer

"Anatoly K. Lasareff" wrote:
>
> Excuse my english :(

Excuse mine too :^)

> I think this is impossible in 'plpgsql', because it is pre-compilrd
> language.

That's what I was fearing... Unfortunatelly that is the only thing that stops me
for implementing the whole trigger set...

What's about PL/TCL ?

I know it can be done in C, but I prefer to use a higher level language. The
proformances are not an issue as triggers run seldom.

> But there is, in my mind, no problem write script on any 'usual' language,
> such as perl. This script can take data from your table, and send nesessary
> SQL queries to server.

Nope, I has to be a trigger :^(

Thanks!

Bye!

--
 Daniele

-------------------------------------------------------------------------------
"MSDOS didn't get as bad as it is overnight -- it took over ten years
of careful development."
(By dmeggins@aix1.uottawa.ca)
-------------------------------------------------------------------------------
 Dal 28-09-1998, con un solo POP abbiamo fatto guadagnare a telecom italia
 15.767.177 Lire solo in scatti alla risposta. Non male eh ?
-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------