How to return resultset with Plpython3 in Postgres

Question

How can a plpython function return result set as a normal sql query resultset (not as text).

Here is function definition -

DROP FUNCTION IF EXISTS demo_report();
CREATE OR REPLACE FUNCTION demo_report()
    RETURNS SETOF <what-type>
AS $$
    rv = plpy.execute("SELECT * FROM test")
    return rv
$$ LANGUAGE plpython3u;

When I execute select demo_report(); it should return the resultset to client rather than text.Right now I am getting this as text - enter image description here

I am using Postgres 9.2 with plpython3u on Windows.

Ravi Kumar · Accepted Answer · 2013-05-04 17:19:30Z

3

I found the way to get desired result - For Table :

CREATE TABLE public.test
(
   id serial NOT NULL,
   name   varchar(200) NOT NULL CHECK (name <> ''),
   salary int,
   created  date,
   CONSTRAINT id PRIMARY KEY (id),
) 
WITH (
  OIDS = FALSE,
  autovacuum_enabled = true
);

My Plpython3u function is -

CREATE OR REPLACE FUNCTION demo_report()
  RETURNS SETOF test
AS $$
  resp = []
  rv = plpy.execute("SELECT * FROM test")
  for i in rv:
    resp.append(i)
  return resp

$$ LANGUAGE 'plpython3u' VOLATILE;

And I query it like -

select * from demo_report();

Now I am getting the desired response - enter image description here

Its been quite nice journey with plpython till now. Enjoying it.

answered May 4, 2013 at 17:19

Ravi Kumar

1,40217 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ravi Kumar Over a year ago

Basically I am returning the type as table name. This works if i am using query SELECT * FROM test; in plpython code. If I do select id,name from test; it breaks. Still need to find a way to tackle it without defining a new type.

JohnMudd Over a year ago

You can create a TYPE instead of a table. stackoverflow.com/a/38315231/487992

Collectives™ on Stack Overflow

How to return resultset with Plpython3 in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related