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
| - - - - - - - - - - - - - - - - - - - - - - - - -
-- vim:set filetype=pgsql:
SET client_min_messages TO "NOTICE";
CREATE TABLE example1 (
"FieldName1" INTEGER PRIMARY KEY
);
INSERT INTO example1 ("FieldName1") VALUES ( 1 );
-- -------------------------------------------------------------------------
CREATE FUNCTION get_field_from_cursor( field TEXT, curs REFCURSOR )
RETURNS INTEGER AS $body$
DECLARE
rec RECORD;
res INTEGER;
BEGIN
RAISE NOTICE 'Getting field %', field;
FETCH curs INTO rec;
--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
--SELECT INTO res field FROM rec; -- ERROR: syntax error at or near "$2"
return res;
END;
$body$ LANGUAGE PLPGSQL;
-- -------------------------------------------------------------------------
CREATE FUNCTION test()
RETURNS VOID AS $body$
DECLARE
curs REFCURSOR;
result INTEGER;
BEGIN
OPEN curs FOR SELECT * FROM example1;
result := get_field_from_cursor( 'FieldName1', curs );
RAISE NOTICE 'Got field %', result;
END;
$body$ LANGUAGE PLPGSQL;
-- -------------------------------------------------------------------------
GRANT SELECT ON example1 TO PUBLIC;
SELECT test();
-- -------------------------------------------------------------------------
DROP TABLE example1;
DROP FUNCTION test();
DROP FUNCTION get_field_from_cursor( TEXT, REFCURSOR);
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
