Postgres Pro
Downloads
Home   >   mailing lists

Thread: Return SETOF or array from pl/python

Return SETOF or array from pl/python

From
Peter Fein
Date:
Is it possible to return a SETOF text or a text[] from pl/python?

I've got the following test cases:

CREATE OR REPLACE FUNCTION arf()
RETURNS text[] LANGUAGE plpythonu AS
$$return ["one", "two", "three"]$$;

SELECT arf();

ERROR:  missing dimension value

CREATE OR REPLACE FUNCTION arf2()
RETURNS text[] LANGUAGE plpythonu AS
$$return '{"one", "two", "three"}'$$;

SELECT arf2();

       arf2
-----------------
 {one,two,three}
(1 row)

CREATE OR REPLACE FUNCTION srf()
RETURNS SETOF text LANGUAGE plpythonu AS
$$return ["one", "two", "three"]$$;

SELECT * FROM srf();

            srf
-------------------------
 ['one', 'two', 'three']
(1 row)

SELECT srf();
Never returns.

I can obviously use something like arf2 (manually stringifying w/i
python) but this seems ugly.  I'd really prefer to return a set, rather
than an array.

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

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

Re: Return SETOF or array from pl/python

From
"Joshua D. Drake"
Date:
Peter Fein wrote:
> Is it possible to return a SETOF text or a text[] from pl/python?
>
> I've got the following test cases:
>
> CREATE OR REPLACE FUNCTION arf()
> RETURNS text[] LANGUAGE plpythonu AS
> $$return ["one", "two", "three"]$$;
>
> SELECT arf();
>
> ERROR:  missing dimension value
>
> CREATE OR REPLACE FUNCTION arf2()
> RETURNS text[] LANGUAGE plpythonu AS
> $$return '{"one", "two", "three"}'$$;
>
> SELECT arf2();
>
>        arf2
> -----------------
>  {one,two,three}
> (1 row)
>
> CREATE OR REPLACE FUNCTION srf()
> RETURNS SETOF text LANGUAGE plpythonu AS
> $$return ["one", "two", "three"]$$;
>
> SELECT * FROM srf();
>
>             srf
> -------------------------
>  ['one', 'two', 'three']
> (1 row)
>
> SELECT srf();
> Never returns.

I am not an everyday python programmer but I am pretty sure that you are
trying to return a list in arf(). You can't return a list you have to
return the array type which is why arf2 works.

The reasons srf works is because you are just returning text in general
regardless that it is formatting to a list.

Sincerely,

Joshua D. Drake


>
> I can obviously use something like arf2 (manually stringifying w/i
> python) but this seems ugly.  I'd really prefer to return a set, rather
> than an array.
>


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Return SETOF or array from pl/python

From
Peter Fein
Date:
Joshua D. Drake wrote:
> Peter Fein wrote:
>
>> Is it possible to return a SETOF text or a text[] from pl/python?
>>
>> I've got the following test cases:
>>
>> CREATE OR REPLACE FUNCTION arf()
>> RETURNS text[] LANGUAGE plpythonu AS
>> $$return ["one", "two", "three"]$$;
>>
>> SELECT arf();
>>
>> ERROR:  missing dimension value
>>
>> CREATE OR REPLACE FUNCTION arf2()
>> RETURNS text[] LANGUAGE plpythonu AS
>> $$return '{"one", "two", "three"}'$$;
>>
>> SELECT arf2();
>>
>>        arf2
>> -----------------
>>  {one,two,three}
>> (1 row)
>>
>> CREATE OR REPLACE FUNCTION srf()
>> RETURNS SETOF text LANGUAGE plpythonu AS
>> $$return ["one", "two", "three"]$$;
>>
>> SELECT * FROM srf();
>>
>>             srf
>> -------------------------
>>  ['one', 'two', 'three']
>> (1 row)
>>
>> SELECT srf();
>> Never returns.
>
>
> I am not an everyday python programmer but I am pretty sure that you are
> trying to return a list in arf(). You can't return a list you have to
> return the array type which is why arf2 works.

Ok.  How does one convert a python list to a PGSql array then?  Is there
a better way to do it than what I did in arf2?

> The reasons srf works is because you are just returning text in general
> regardless that it is formatting to a list.

How does one return a set then?  I want as my output:

SELECT * FROM srf();

             srf
 -------------------------
  'one'
  'two'
  'three'
 (3 rows)

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

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

Re: Return SETOF or array from pl/python

From
"Joshua D. Drake"
Date:
>>
>>I am not an everyday python programmer but I am pretty sure that you are
>>trying to return a list in arf(). You can't return a list you have to
>>return the array type which is why arf2 works.
>
>
> Ok.  How does one convert a python list to a PGSql array then?  Is there
> a better way to do it than what I did in arf2?

I don't think you can with plPython at least not without reformatting
the list within the function itself.

>
>
>>The reasons srf works is because you are just returning text in general
>>regardless that it is formatting to a list.
>
>
> How does one return a set then?  I want as my output:

You would actually have to have a set. What I believe you are trying to
do is transform a list to a result set. I don't think you can do that
without some additional programming within the function.

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.

Sincerely,

Joshua D. Drake


>
> SELECT * FROM srf();
>
>              srf
>  -------------------------
>   'one'
>   'two'
>   'three'
>  (3 rows)
>


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Return SETOF or array from pl/python

From
Peter Fein
Date:
Joshua D. Drake wrote:

> You would actually have to have a set. What I believe you are trying to
> do is transform a list to a result set. I don't think you can do that
> without some additional programming within the function.
>
> 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.

Is there a way to represent a set of constant rows in SQL, aside from
creating a temp table & populating it?  I've had need of this before -
IIRC, something was mentioned about the SQL VALUES construct being
unimplemented.

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

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

Re: Return SETOF or array from pl/python

From
Peter Fein
Date:
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