python apply 返回多列,PL / Python和postgreSQL：返回包含多列的表的最佳方法是什么？...-优快云博客

In Pl/Python "RETURNS setof" or "RETURNS table" clause are used to return a table like structured data. It seems to me that one has to provide the name of each column to get a table returned. If you have a table with a few columns it is an easy thing. However, if you have a table of 200 columns, what's the best way to do that? Do I have to type the names of all of columns (as shown below) or there is a way to get around it? Any help would be much appreciated.

Below is an example that uses "RETURNS table" clause. The code snippets creates a table (mysales) in postgres, populate it and then use Pl/Python to fetch it and returning the column values. For simplicity I am only returning 4 columns from the table.

DROP TABLE IF EXISTS mysales;

CREATE TABLE mysales (id int, year int, qtr int, day int, region

text) DISTRIBUTED BY (id);

INSERT INTO mysales VALUES

(1, 2014, 1,1, 'north america'),

(2, 2002, 2,2, 'europe'),

(3, 2014, 3,3, 'asia'),

(4, 2010, 4,4, 'north-america'),

(5, 2014, 1,5, 'europe'),

(6, 2009, 2,6, 'asia'),

(7, 2002, 3,7, 'south america');

DROP FUNCTION IF EXISTS myFunc02();

CREATE OR REPLACE FUNCTION myFunc02()

RETURNS TABLE (id integer, x integer, y integer, s text) AS

rv = plpy.execute("SELECT * FROM mysales ORDER BY id", 5)

d = rv.nrows()

return ( (rv[i]['id'],rv[i]['year'], rv[i]['qtr'], rv[i]['region'])

for i in range(0,d) )

$$ LANGUAGE 'plpythonu';

SELECT * FROM myFunc02();

#Here is the output of the SELECT statement:

1; 2014; 1;"north america"

2; 2002; 2;"europe"

3; 2014; 3;"asia"

4; 2010; 4;"north-america"

5; 2014; 1;"europe"

6; 2009; 2;"asia"

7; 2002; 3;"south america"

解决方案

Try this:

CREATE OR REPLACE FUNCTION myFunc02()

RETURNS TABLE (like mysales) AS

rv = plpy.execute('SELECT * FROM mysales ORDER BY id;', 5)

d = rv.nrows()

return rv[0:d]

$$ LANGUAGE 'plpythonu';

which returns:

gpadmin=# SELECT * FROM myFunc02();

id | year | qtr | day | region

----+------+-----+-----+---------------

1 | 2014 | 1 | 1 | north america

2 | 2002 | 2 | 2 | europe

3 | 2014 | 3 | 3 | asia

4 | 2010 | 4 | 4 | north-america

5 | 2014 | 1 | 5 | europe

(5 rows)

Something to consider for MPP like Greenplum and HAWQ is to strive for functions that take data as an argument and return a result, rather than originating the data in the function itself. The same code executes on every segment so occasionally there can be unintended side effects.

Update for SETOF variant:

CREATE TYPE myType AS (id integer, x integer, y integer, s text);

CREATE OR REPLACE FUNCTION myFunc02a()

RETURNS SETOF myType AS

# column names of myType ['id', 'x', 'y', 's']

rv = plpy.execute("SELECT id, year as x, qtr as y, region as s FROM mysales ORDER BY id", 5)

d = rv.nrows()

return rv[0:d]

$$ LANGUAGE 'plpythonu';

Note, to use the same data from the original example, I had to alias each of the columns to corresponding names in myType. Also, you'll have to enumerate all of the columns of mysales if going this route - there isn't a straightforward way to CREATE TYPE foo LIKE tableBar although you might be able to use this to alleviate some of the manual work of enumerate all the names/types:

select string_agg(t.attname || ' ' || t.format_type || ', ') as columns from

(

SELECT a.attname,

pg_catalog.format_type(a.atttypid, a.atttypmod),

(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)

FROM pg_catalog.pg_attrdef d

WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),

a.attnotnull, a.attnum,

a.attstorage ,

pg_catalog.col_description(a.attrelid, a.attnum)

FROM pg_catalog.pg_attribute a

LEFT OUTER JOIN pg_catalog.pg_attribute_encoding e