access computed field of RECORD variable

Steve White wrote:

The roadblock is:

There is a record, which may be from any of a set of similar databases.
Field names for this record are computed as strings. Using these strings,
I need to access fields of the record.

But I can't find the syntax for it.

You can't do this with plpgsql. There are some limited possibilities
with "EXECUTE 'foo bar $1 baz' USING rec" where the $1 is expanded with
the record. However the best thing you can do is use a different PL,
probably PL/Perl.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Tue, Jan 5, 2010 at 8:53 AM, Steve White <swhite@aip.de> wrote:

Hi,

I ran into a roadblock at the very bottom of a fairly large database
design implementation.  It will be at least messy to fix, unless there is
a neat solution.

The roadblock is:

There is a record, which may be from any of a set of similar databases.
Field names for this record are computed as strings.  Using these strings,
I need to access fields of the record.

But I can't find the syntax for it.

Now, if a record variable is a known row type, I can get a field of a
computed name.
For a generic RECORD I can get a field by putting an explicit name the code,
       (rec).FieldName1
But can one get the value of a computed field from a generic RECORD?

why are you using generic records then? :-) Maybe there is an elegant
solution to your issue with a bit more context.

merlin

Merlin,

There is a set of databases whose columns are differently named and
have different meanings, but which are processed similarly (the data
is all floats, and some dot products need to be made).
The processing to be done can all described by other tables which
provide coefficents based on the names of the fields in those columns.

Now, I have gone so far as to pass explicit query strings around, to make
a separate query for each field. But the programming is awful compared
to the "cursors" approach, besides probabaly being quite inefficient.

One can also do an EXECUTE INTO but it has the same problem that the
type of the record is unknown (Well, it might be known at run time...
If I knew the type of the record at run time, could use that somehow?).

Another step would be to scrap the whole idea of named columns for these
databases, but that would be a pity, because it bought us so much in
other ways (e.g. tight association of the interpretation of the columns
with their data). Besides, I already wrote code for all that!

Any ideas?

On 5.01.10, Merlin Moncure wrote:

On Tue, Jan 5, 2010 at 8:53 AM, Steve White <swhite@aip.de> wrote:

Hi,

I ran into a roadblock at the very bottom of a fairly large database
design implementation. �It will be at least messy to fix, unless there is
a neat solution.

The roadblock is:

There is a record, which may be from any of a set of similar databases.
Field names for this record are computed as strings. �Using these strings,
I need to access fields of the record.

But I can't find the syntax for it.

Now, if a record variable is a known row type, I can get a field of a
computed name.
For a generic RECORD I can get a field by putting an explicit name the code,
� � � �(rec).FieldName1
But can one get the value of a computed field from a generic RECORD?

why are you using generic records then? :-) Maybe there is an elegant
solution to your issue with a bit more context.

merlin

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| e-Science / AstroGrid-D Zi. 35 Bg. 20
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -