Thread: Function returning SETOF using plpythonu
Hi, Is it possible to return rows from a function written in plpythonu using SETOF? Example: CREATE FUNCTION "test_python_setof"() RETURNS SETOF text AS ' records=plpy.execute("SELECT name FROM interface"); return records ' LANGUAGE 'plpythonu'; With this code is returning the object from the execution: <PLyResult object at 0xb703e458> Best regards, Luís Sousa
Dnia Fri, 26 Jan 2007 17:24:52 +0000, Luís Sousa napisał(a): > Hi, > > Is it possible to return rows from a function written in plpythonu using > SETOF? > > Example: > CREATE FUNCTION "test_python_setof"() > RETURNS SETOF text AS ' > records=plpy.execute("SELECT name FROM interface"); > return records > ' LANGUAGE 'plpythonu'; > > With this code is returning the object from the execution: > <PLyResult object at 0xb703e458> plpy.execute returns dictionary, and you need a list. You may try this: CREATE FUNCTION "test_python_setof"() RETURNS SETOF text AS ' records=plpy.execute("SELECT name FROM interface"); return [ (r["name"]) for r in records] ' LANGUAGE 'plpythonu'; -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org | So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org
Thanks :-) That worked fine. >plpy.execute returns dictionary, and you need a list. You may try this: > >CREATE FUNCTION "test_python_setof"() >RETURNS SETOF text AS ' > records=plpy.execute("SELECT name FROM interface"); > return [ (r["name"]) for r in records] >' LANGUAGE 'plpythonu'; > > > Then I tried to do some changes and try to return a SETOF type: CREATE TYPE "test_python_t" AS ( name varchar(50) ); CREATE FUNCTION "test_python_setof"() RETURNS SETOF test_python_t AS ' records=plpy.execute("SELECT name FROM interface"); return [ (r["name"]) for r in records] ' LANGUAGE 'plpythonu'; And I'm getting ERROR: tuple return types are not supported yet. On my production database server I'm using PostgreSQL 7.4 and using language plpgsql I'm returning some SETOF type without problems. Is this a feature that's missing on this version or I'm I doing something wrong on code? If is a feature missing, is already implemented on some version afterwards? Best regards, Luís Sousa
On Monday 29 January 2007 6:12 am, Luís Sousa wrote: > Thanks :-) > That worked fine. > > >plpy.execute returns dictionary, and you need a list. You may try this: > > > >CREATE FUNCTION "test_python_setof"() > >RETURNS SETOF text AS ' > > records=plpy.execute("SELECT name FROM interface"); > > return [ (r["name"]) for r in records] > >' LANGUAGE 'plpythonu'; > > Then I tried to do some changes and try to return a SETOF type: > CREATE TYPE "test_python_t" AS ( > name varchar(50) > ); > > CREATE FUNCTION "test_python_setof"() > RETURNS SETOF test_python_t AS ' > records=plpy.execute("SELECT name FROM interface"); > return [ (r["name"]) for r in records] > ' LANGUAGE 'plpythonu'; > > And I'm getting ERROR: tuple return types are not supported yet. > > On my production database server I'm using PostgreSQL 7.4 and using > language plpgsql I'm returning some SETOF type without problems. > Is this a feature that's missing on this version or I'm I doing > something wrong on code? If is a feature missing, is already implemented > on some version afterwards? > > Best regards, > Luís Sousa > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org SETOF in pl/pythonu appeared in 8.2 -- Adrian Klaver aklaver@comcast.net