Thread: 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
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