Thread: access computed field of RECORD variable
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?
With
rec RECORD;
field TEXT;
The following all fail:
res := rec.field; -- ERROR: record "rec" has no field "field"
res := (rec.field); -- ERROR: record "rec" has no field "field"
res := (rec).field; -- ERROR: syntax error at or near "$2"
res := rec.(field); -- ERROR: syntax error at or near "("
res := rec."FieldName1"; -- gets field, but isn't what is needed
I also tried numerous other things that were doomed to fail.
Find an example file attached. Just "\i" it and play with the possibilites.
Thanks!
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| 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
| - - - - - - - - - - - - - - - - - - - - - - - - -
Attachment
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 | - - - - - - - - - - - - - - - - - - - - - - - - -
Alvaro, I followed your advice, but using PL/Python. I succeeded, but only with great difficulty. To close this off, I'll write these down, together with the work-arounds. Some of this info would be of use if it were in the documentation. Problems -------- 1) (documentation) The doc says Python functions can't be called directly from Python! But more accurately, PostgreSQL functions written in Python can't be called from Python. 2) Tragically, the plpy.execute() function flattens composite fields to string. In my case a work-around was simple. For others, this will be a killer. 3) PL/Python doesn't permit functions that return RECORD or SETOF RECORD. But I want to return a generic table... (or do I?) Work-arounds ------------ 1) Python is one of those languages that allow functions to be defined within the body of another function. So within the body of a PostgreSQL function definition, you can build a little Python environment containing everything you would expect: functions, classes, etc, and proceed to do serious programming. One can basically treat the body of a PL/Python function as an individual Python script, with the 'plpy' module already included. 2) I wrote about this in a different posting, that nobody has yet answered. In my case, I was able to parse the string without too much trouble. But this is really a crying shame. 3a) So PL/Python doesn't return SETOF RECORD. The trick I implemented was to call it from a PL/pgSQL which created a temp table, which was filled by the Python. This function in turn could return the set of generic records using RETURN NEXT. It would be nice PL/Python could return RECORD--I don't know why it shouldn't. 3b) In my case though, It didn't matter. I ended up needing functions that returned tables of a set of known types anyway. But the set is large, and unfortunately, there is no way to pass the return type of a function as a parameter. It has to be known at create time. The solution was, to write a function that did a CREATE FUNCTION to create a function of the desired types. This way for each known table, only one SELECT needs to be done, to create the needed function for that table. Thanks! -- | - - - - - - - - - - - - - - - - - - - - - - - - - | 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 | - - - - - - - - - - - - - - - - - - - - - - - - -