你有两个选择:使用set-returns函数返回“record”或返回你的自定义类型 .
第一种选择:
create table test (a int, b int, c int, d varchar, e varchar, f varchar);
insert into test select id, id*2, id*3, (id*4)::varchar, (id*4)::varchar, (id*4)::varchar from generate_series(1,10) id;
create or replace function test_func(column_list varchar[]) returns setof record as $BODY$
declare
r record;
begin
for r in execute 'select ' || array_to_string(column_list, ',') || ' from test' loop
return next r;
end loop;
return;
end;
$BODY$
language plpgsql
volatile;
select * from test_func(array['a','c','e']) as f(a int, c int, e varchar);
第二种选择:
create table test (a int, b int, c int, d varchar, e varchar, f varchar);
insert into test select id, id*2, id*3, (id*4)::varchar, (id*4)::varchar, (id*4)::varchar from generate_series(1,10) id;
create type testtype as (
a int,
c int,
e varchar
);
create or replace function test_func() returns setof testtype as $BODY$
declare
r testtype;
begin
for r in execute 'select a,c,e from test' loop
return next r;
end loop;
return;
end;
$BODY$
language plpgsql
volatile;
select * from test_func();
但我99%肯定你是在尝试做错事 . 在Greenplum中,函数执行的结果不能在连接条件中用作“表”,因为函数在主服务器上执行 . 您甚至无法从最后一个查询中创建一个表,因为这个限制从函数返回数据简而言之,这不是一种在Greenplum中处理数据的推荐方法