时间戳

首页 > 解决方案 > Postgres函数返回未知数量的列

json - Postgres函数返回未知数量的列

问题描述

我一直在寻找一种方法来创建一个函数，它将 json 作为输入，我将在其中指定列和表名，并将这些列作为输出。

假设我有下表：

 col1 | col2  | col3  
------+-------+-------
    1 | 303.8 | 331.9
    5 | 373.2 | 765.8
    1 | 467.4 | 427.5
    5 | 139.5 | 500.5
    4 | 350.6 | 706.2

我想这样调用一个函数：

SELECT foo('{"dimensions":["col2", "col3"], "table":"test"}');

这将给出：

 col2  | col3  
-------+-------
 303.8 | 331.9
 373.2 | 765.8
 467.4 | 427.5
 139.5 | 500.5
 350.6 | 706.2

问题是我不确定我的输出应该是什么，因为它不能是“表”，因为在执行函数之前我不知道列及其类型。

CREATE OR REPLACE FUNCTION foo ( j json ) RETURNS ??? AS
$$

DECLARE
   dimensions json ARRAY;
   sql_string TEXT;
   field TEXT;
   table_name TEXT;

BEGIN
   dimensions := ARRAY(SELECT json_array_elements(j->'dimensions'));
   table_name := TEXT ( j->> 'table' );

   sql_string := 'SELECT';

   FOREACH field IN ARRAY dimensions LOOP
      sql_string:= sql_string || ' ' || field || ',';
   END LOOP;
   SELECT TRIM (trailing ',' from sql_string) INTO sql_string;

   sql_string := sql_string || ' FROM ' || table_name ;

   EXECUTE sql_string INTO ???;
   RETURN ???;
END
$$ LANGUAGE plpgsql;

谢谢！

标签： jsonpostgresqlplpgsql

解决方案

简而言之，refcursor & 动态查询。

做你的功能RETURNS refcursor。

在DECLARE部分：

_str text;
_cur refcursor = 'name_of_refcursor_1';

在代码中：

_str = 'SELECT ';
FOR _column IN <array or select to make column list>
LOOP
  _str = _str || _column.name || ', '
END LOOP;
_str = TRIM(TRAILING ', ' FROM _str);
_str = _str || ' FROM my_table1';

OPEN _cur FOR
EXECUTE _str;

RETURN _cur;

功能外：

FETCH ALL FROM "name_of_refcursor_1";

或者

FOR _row IN FETCH ALL FROM "name_of_refcursor_1"
...

推荐阅读