Hi,
I recently wrote PL/Python code that worked on fields of composite types.
The plpy.execute() command on a SELECT returns a list of nice dictionaries
keyed on field names, containing the fields. For numeric types, the type
of the dictionary values are as expected. To my chagrin however, if a
field contains a composite type, it is flattened to a string.
I would have expected a composite type field to be returned as a dictionary
of values of the proper types, keyed on the names of the elements of the
composite type.
I was able to work around this problem in an ugly way, but I can imaging
cases where this would render plpy unsuitable.
I see nothing in the documentation about this
http://www.postgresql.org/docs/8.4/static/plpython.html
It only talks about passing composite types into and out of functions.
Is this a bug? This is psql v. 8.1.18 on one machine, and 8.4.1 on another.
Am I somehow doing it wrong?
-- See attached test
Cheers!
--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| 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:
/** Illustrates PL/Py flattening of composite types
* Log in as user 'postgres' in directory containing this file, start psql,
then
create language plpythonu; -- if haven't done already
\i pycomptype.sql
select pycomptypes.color_read( 1 );
* To get rid of the tables
drop schema pycomptypes cascade;
*/
CREATE SCHEMA pycomptypes AUTHORIZATION postgres;
SET search_path TO pycomptypes;
CREATE TYPE value_sigma AS (
value DOUBLE PRECISION,
sigma DOUBLE PRECISION
);
CREATE TABLE colors (
colors_id INTEGER PRIMARY KEY,
red value_sigma,
green value_sigma,
blue value_sigma
);
INSERT INTO colors VALUES ( 1, (1.21, 0.05), (1.45, 0.06), (1.83, 0.07) );
INSERT INTO colors VALUES ( 2, (0.94, 0.05), (0.38, 0.03), (1.81, 0.07) );
INSERT INTO colors VALUES ( 3, (0.56, 0.02), (0.74, 0.05), (1.90, 0.08) );
CREATE OR REPLACE FUNCTION
color_read( color_id INTEGER )
RETURNS VOID AS $$
cmd = 'SELECT red,green,blue FROM colors WHERE colors_id=' + str( color_id )
for t in plpy.execute( cmd ):
plpy.notice( 'type of color item: %s' % ( type( t['red'] ) ) )
$$ LANGUAGE PLPYTHONU;
GRANT SELECT ON colors TO PUBLIC;
GRANT USAGE ON SCHEMA pycomptypes TO PUBLIC;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
