PLPython & CAST composite data type in Postgresql

Question

I am writing a stored procedure in PlPython with a user defined type. I know Plpython does not support user defined types, so, I have created a CAST for the user defined type. Still I keep getting an error when I call plpy.prepare. I am not sure if I am using the CAST incorrectly - the example code is below:

#User Defined Type - person
CREATE TYPE person As( name character varying(50), state  character(2));

#Table definition using 'person'
CREATE TABLE manager As(id integer, mgr person)

#CAST for person
CREATE OR REPLACE FUNCTION person_to_text(person) RETURNS text AS 'SELECT ROW($1.*)::text' LANGUAGE SQL;
CREATE CAST (cv_person as text) WITH FUNCTION person_to_text(person)

#PlPython procedure

CREATE OR REPLACE FUNCTION load_portfolio_assoc (name text, state text) RETURNS integer AS $$

  mgr_str ="('"+name+"','"+state+"')"

  insert_qry = 'Insert into manager (mgr) values($1)'
  value_type = ['text']

  qry = plpy.prepare(insert_qry,value_type)   
  rv  = plpy.execute(qry, [mgr_str])

  return 1


  $$ LANGUAGE plpython3u;

rhm2012 · Accepted Answer · 2012-02-14 01:33:17Z

2

An update :

Plpython accepts the query when it is written as follows with the user defined type specified with the variable in this format $1:: and stops throwing composite type not supported exceptions,

insert_qry = 'Insert into manager (mgr) values($1::person)'
value_type = ['text']

At the end, I really didn't have to do any extra casting operation on the database. It worked just by tweaking the variable as above.

edited Feb 14, 2012 at 1:33

answered Feb 12, 2012 at 18:50

rhm2012

5941 gold badge5 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PLPython & CAST composite data type in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related