Re: Return SETOF or array from pl/python

Joshua D. Drake wrote:
> Somebody may know of a better way but what I would think would happen is
> this:
>
> Break up list, insert each value of list into a temp table as a row,
> return set of temp table.

Ok. I tried this & ran in to some trouble:

CREATE OR REPLACE FUNCTION setret(text)
  RETURNS SETOF record AS
$BODY$plpy.execute("""
    CREATE TEMP TABLE my_temp
    (
    clean_text text NOT NULL
    ) WITHOUT OIDS
    ON COMMIT DROP;
    """)

for i in text.split():
    plpy.execute("INSERT INTO my_temp VALUES (%s)"%i.lower())

# Do SQL stuff with my_temp - JOIN it to permanent tables, etc..

return plpy.execute("SELECT * FROM my_temp")$BODY$
  LANGUAGE 'plpythonu' STABLE STRICT;
ALTER FUNCTION setret(text) OWNER TO postgres;

SELECT setret('foo BAR baz Quux');
ERROR:  plpython functions cannot return type record

SELECT * FROM setret('foo BAR baz Quux');
ERROR:  a column definition list is required for functions returning
"record"

After much googling, I found
http://archives.postgresql.org/pgsql-general/2005-03/msg01488.php
which indicates it can't be done. Any suggestions?

I suppose I could have my python function return an array of its
processed values and then write a plsql function that loops over it,
returning records (unless there's a builtin to do that?). I need to do
further SQL operations on the output of the python function (JOINs,
etc.). This seems ugly/slow. The whole mess eventually goes in to a
trigger, so doing it client-side isn't an option.

--
Peter Fein                 pfein@pobox.com                 773-575-0694

Basically, if you're not a utopianist, you're a schmuck. -J. Feldman