返回动态列集 [英] Return dynamic set of columns

问题描述

我创建了以下函数以根据该函数的参数返回一组列:

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;

使用示例表:

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

我确实有以下问题:

• 如何扩展此函数以将任意数量的列用作输入(当前我仅限于4个列)，例如:

• 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')

当前，如果我需要少于4列，我必须使用无"作为参数值，有什么方法可以避免这种情况?我认为这将通过回答上一个问题自动解决

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

推荐答案

如果所有列共享同一类型，则可以使用 arrays .

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.

这篇关于返回动态列集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！