PL / Python和postgreSQL：返回包含多列的表的最佳方法是什么？ [英] PL/Python & postgreSQL: What is the best way to return a table of many columns?
问题描述
在Pl / Python中， RETURNS setof或 RETURNS table子句用于返回类似于结构化数据的表。在我看来，必须提供每一列的名称才能返回表。如果您的表有几列，这是一件容易的事。但是，如果您有一个包含200列的表格，那么最好的方法是什么？我是否必须键入所有列的名称（如下所示），还是有一种解决方法？任何帮助将非常感激。
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.
下面是一个使用 RETURNS table子句的示例。代码段在postgres中创建一个表（mysales），将其填充，然后使用Pl / Python来获取该表并返回列值。为简单起见，我仅从表中返回4列。
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"
推荐答案
尝试一下：
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';
返回：
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)
像Greenplum和HAWQ这样的MPP需要考虑的事情对于将数据作为参数并返回结果的函数，而不是在函数本身中引发数据。在每个段上执行相同的代码，因此偶尔会有意外的副作用。
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.
更新 SETOF 变体：
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';
注意，要使用原始示例中的相同数据，我必须将每个列都使用别名 myType 中的相应名称。此外，如果要使用此路线，您还必须枚举 mysales 的所有列-没有简单的方法来创建创建类型foo tableBar ，尽管您可以使用它来减轻枚举所有名称/类型的一些手动工作：
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
ON e.attrelid = a .attrelid AND e.attnum = a.attnum
WHERE a.attrelid = (SELECT oid FROM pg_class WHERE relname = 'mysales') AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
) t ;
返回：
columns
-------------------------------------------------------------------
id integer, year integer, qtr integer, day integer, region text,
(1 row)
这篇关于PL / Python和postgreSQL：返回包含多列的表的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
