Обсуждение: Return SETOF or array from pl/python
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
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/
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
>> >>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/
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
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