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 -

I am using Postgres 9.2 with plpython3u on Windows.

Answer 1

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 -

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