pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

how to returns set of records in PL/python
3+ messages / 2 participants
[nested] [flat]

* how to returns set of records in PL/python
@ 2008-04-07 12:47  Anoop G <anoopmadavoor@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Anoop G @ 2008-04-07 12:47 UTC (permalink / raw)
  To: pgsql-sql

Hi ,

  Iam new to plpython,how can I return a  recordset from a plpython
function?

  Is there is any way give me an example;


plpgsql function
--------------------------------

CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF
RECORD AS $BODY$
DECLARE

    r RECORD;

BEGIN

    FOR r IN SELECT  pk_bint_country_id,vchr_country FROM tbl_country LOOP
        RETURN NEXT r;
        END LOOP;
    RETURN;

END
$BODY$ LANGUAGE 'plpgsql';




How i can get the same result using plpythonu and how I can call the
function (is there any user defined type required like in plpgsql)?

Thanks in advance

Anoop

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: how to returns set of records in PL/python
@ 2008-04-07 13:03  Gerardo Herzig <gherzig@fmed.uba.ar>
  parent: Anoop G <anoopmadavoor@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Gerardo Herzig @ 2008-04-07 13:03 UTC (permalink / raw)
  To: Anoop G <anoopmadavoor@gmail.com>; pgsql-sql

Anoop G wrote:

>Hi ,
>
>  Iam new to plpython,how can I return a  recordset from a plpython
>function?
>
>  Is there is any way give me an example;
>
>
>plpgsql function
>--------------------------------
>
>CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF
>RECORD AS $BODY$
>DECLARE
>
>    r RECORD;
>
>BEGIN
>
>    FOR r IN SELECT  pk_bint_country_id,vchr_country FROM tbl_country LOOP
>        RETURN NEXT r;
>        END LOOP;
>    RETURN;
>
>END
>$BODY$ LANGUAGE 'plpgsql';
>
>
>
>
>How i can get the same result using plpythonu and how I can call the
>function (is there any user defined type required like in plpgsql)?
>
>Thanks in advance
>
>Anoop
>
>  
>
Yes, plpython can return setofs. You may need to define a new type for that.
After that you can return a list, tuple, dictionary, set, generator 
object, or any

You may also check the plpython version, as i recall, its kind of *new* 
issue.

This works on 8.2.5, python 2.5,

For a simple case, something like that would work
regression=# \d countries
           Table "public.countries"
    Column    |       Type        | Modifiers
--------------+-------------------+-----------
 country_id   | integer           |
 country_name | character varying |


CREATE OR REPLACE FUNCTION get_countries()
returns setof countries
security definer
as
$$
return plpy.execute("select * from countries")

$$ language plpythonu;

Hope that helps.
Gerardo




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: how to returns set of records in PL/python
@ 2008-04-07 13:04  Gerardo Herzig <gherzig@fmed.uba.ar>
  parent: Anoop G <anoopmadavoor@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Gerardo Herzig @ 2008-04-07 13:04 UTC (permalink / raw)
  To: Anoop G <anoopmadavoor@gmail.com>; +Cc: pgsql-sql

Anoop G wrote:

>Hi ,
>
>  Iam new to plpython,how can I return a  recordset from a plpython
>function?
>
>  Is there is any way give me an example;
>
>
>plpgsql function
>--------------------------------
>
>CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF
>RECORD AS $BODY$
>DECLARE
>
>    r RECORD;
>
>BEGIN
>
>    FOR r IN SELECT  pk_bint_country_id,vchr_country FROM tbl_country LOOP
>        RETURN NEXT r;
>        END LOOP;
>    RETURN;
>
>END
>$BODY$ LANGUAGE 'plpgsql';
>
>
>
>
>How i can get the same result using plpythonu and how I can call the
>function (is there any user defined type required like in plpgsql)?
>
>Thanks in advance
>
>Anoop
>
>  
>
Forgot to mention that postgres home page has some info about plpython.
http://www.postgresql.org/docs/8.3/static/plpython-funcs.html

Cya.
Gerardo



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2008-04-07 13:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-07 12:47 how to returns set of records in PL/python Anoop G <anoopmadavoor@gmail.com>
2008-04-07 13:03 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2008-04-07 13:04 ` Gerardo Herzig <gherzig@fmed.uba.ar>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox