-- 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;