3

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
  • 1,382
  • 16
  • 22

1 Answers1

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.

Ravi Kumar
  • 1,382
  • 16
  • 22
  • 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. – Ravi Kumar May 04 '13 at 17:21
  • 1
    You can create a TYPE instead of a table. https://stackoverflow.com/a/38315231/487992 – JohnMudd Jul 19 '17 at 21:18