Обсуждение: dynamic plpgsql command on a record type

Hi,

in my plpgsql function I'm looping through tables and their fields and i want to return their field names and the corresponding values.
It all works fine, except i can't retrieve the values, which is really a bummer.
I tried a couple of things (in vain), of which this seemed the most promising:

<<records>>
FOR t_record IN EXECUTE
  t_qstring
LOOP
  <<fields>>
  FOR t_i IN 1..array_upper(t_fields, 1)
    LOOP
      t_rec_out.field_name := t_fields[t_i];
      --retrieve the value of this column, this record, this table into "field_value" for return.
      t_qstring := 't_record.'||t_fields[t_i]; --re-using t_qstring!
      EXECUTE t_qstring INTO t_rec_out.field_value; --<==== happens here ===
      RETURN NEXT t_rec_out;

On EXECUTE, i get the error:
ERROR:  syntax error at or near "t_record"

I also tried:

<<records>>
FOR t_record IN EXECUTE
  t_qstring
LOOP
  <<fields>>
  FOR t_i IN 1..array_upper(t_fields, 1)
    LOOP
      t_rec_out.field_name := t_fields[t_i];
      --retrieve the value of this column, this record, this table into "field_value" for return.
      t_rec_out.field_value := t_record.t_fields[t_i]; --<==== happens here ===
      RETURN NEXT t_rec_out;

And then i get the error:
ERROR:  record "t_record" has no field "t_fields"

Any tips there?

Cheers,

WBL

--
"Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw

On 09/02/10 11:07, Willy-Bas Loos wrote:
> Hi,
>
> in my plpgsql function I'm looping through tables and their fields and i
> want to return their field names and the corresponding values.
> It all works fine, except i can't retrieve the values, which is really a
> bummer.
> I tried a couple of things (in vain), of which this seemed the most
> promising:
...
>        t_rec_out.field_value := t_record.t_fields[t_i]; --<==== happens here
...
> And then i get the error:
> ERROR:  record "t_record" has no field "t_fields"
>
> Any tips there?

This isn't going to be practical in plpgsql. You can just about do it if
you want to build a "SELECT" statement and cast everything to text. Not
worth the trouble though. The problem is that plpgsql is very static in
its typing and just doesn't have the flexibility for this sort of task.

Use pltcl or plperl or plpython or one of the other more dynamic
languages. It's straightforward then.

--
   Richard Huxton
   Archonet Ltd