Return dynamic set of columns

Question

I have created the following function to return set of columns based on parameters of that function:

CREATE OR REPLACE FUNCTION getColumns(IN _column1 text, IN _column2 text, IN _column3 text, IN _column4 text, IN _table text)
  RETURNS TABLE(cmf1 text, cmf2 text, cmf3 text, cmf4 text) AS
$BODY$
BEGIN
    RETURN QUERY EXECUTE 
        'SELECT ' 
            || case when _column1 = 'None' then quote_literal('None') else quote_ident(_column1) end || '::text as cmf1,' 
            || case when _column2 = 'None' then quote_literal('None') else quote_ident(_column2) end || '::text as cmf2,' 
            || case when _column3 = 'None' then quote_literal('None') else quote_ident(_column3) end || '::text as cmf3,'   
            || case when _column3 = 'None' then quote_literal('None') else quote_ident(_column3) end || '::text as cmf4'    
        ' FROM '
            ||  _table; 
END;
 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Using sample table:

CREATE TABLE test20130205
(
  a text,
  b text,
  c character varying,
  d text
);

I can use the function in the following way:

select * from getColumns('a','b','c','d','test20130205');

I do have the following questions:

• How can I extend this function to take any number of columns as input (currently I'm limited to 4), something like:

  getColumns([textColumn1,...,textColumnN],'table')
  

• Currently I have to use 'None' as a parameter value in case I need less then 4 columns, is there a way how to avoid this? I think this will be solved automatically by answering previous question

• Can I somehow preserve data types in output? If not, can I use more array parameters? The function would then look like:

  getColumns(
    [textColumn1,...,textColumnN],
    [numericColumn1,...,numericColumnM],
    [dateColumn1,...,dateColumnO],
    [intColumn1,...,intColumnP],
    'table'
  )
  

Separator

Possible duplicate: https://mcmap.net/q/28848/-refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/939860 – Bubalo

Answer 1

If all columns share the same type, you could operate with arrays.

A more flexible solution would be to use a polymorphic function, hand in a parameter of type anyelement and have the function return the same polymorphic type. This parameter can be of a well-known composite type ...

This is rather advanced server-side programming. You can find code examples for both approaches with explanation and links in this closely related answer.

Bubalo

Thank you, I'll post some solution once I figure it out. – Separator