Greenplum/Postgres 8 function dynamic result set?

Question

I need to write a function that returns a table with unknown number of columns. If i receive 'None' in column input parameter then that column shouldn't be included in the output. In postgres 9+ there is a solution for this problem.

something like below:

CREATE OR REPLACE FUNCTION data_of(id integer,col1 varchar,col2 varchar, col3 varchar)
 RETURNS TABLE (count_rec, dimensions text[] ) AS
$func$
DECLARE

  _dimensions text := 'col1, col2, col3'; -- If i receive 'None' in input param then i exclude that from column list

BEGIN
  RETURN QUERY EXECUTE format('
  SELECT count(*) as count_rec,
        string_to_array($1)  -- AS dimensions
  FROM   x
  WHERE  id = $2'
, _dimensions)
USING  _dimensions , _id;
END
$func$ LANGUAGE plpgsql;

But in Greenplum (Postgres 8.2) i could not find any. Is there any similar solution?

thanks

score 0 · Accepted Answer · answered Aug 06 '15 at 08:18

You have 2 options to do it: use set-returning function returning "record" or returning your custom type.

First option:

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);

Second option:

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();

But I'm 99% sure you're trying to do something wrong. In Greenplum the result of function execution cannot be used as a "table" in join conditions, because the function executes on the master. You even won't be able to create a table out of the last query returning the data from your function because of this limitation In short, this is not a recommended way to work with data in Greenplum

Greenplum/Postgres 8 function dynamic result set?

1 Answers1