主页 / dba / 问题 / 53044
Accepted
A-K
Asked: 2013-11-09 13:42:20 +0800 CST

还有其他方法可以选择动态列列表吗？

  • 772

我需要让我的用户指定他们想要选择的列列表。到目前为止，我知道实现这一目标的两种方法。

1. 使用反射器

CREATE OR REPLACE FUNCTION selecttestwithcolumnlist(
ticker character varying, 
columnlist character varying)
  RETURNS refcursor AS
$BODY$
DECLARE 
  ref1 refcursor;
BEGIN

OPEN ref1 FOR EXECUTE 
'select ' || ColumnList || ' from Prices WHERE Ticker=$1;'
USING     Ticker;
RETURN ref1;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE

这个函数很容易从我的 Ado.Net 客户端调用。我需要做的就是传递参数。但是，如果我想从 pgAdmin 测试这个函数，只有当我保持我的事务打开时，结果集才会在屏幕上打开。这很不方便。当然，将数据公开为 HTML 表格或 Excel 电子表格很容易，但这有点不便。

2.使用记录集

CREATE OR REPLACE FUNCTION SelectPrices(colList VARCHAR)
 RETURNS SETOF record AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || ' FROM prices ORDER BY Ticker, ASOfDate';

END
$func$ LANGUAGE plpgsql;

不幸的是，这使我的客户端代码复杂化。我不能像这样发出一个简单的 SELECT：

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') ;

我必须明确提供我的结果集的结构：

SELECT price,AsOfdate,ticker FROM SelectPrices('price,AsOfdate,ticker') 
AS f(price NUMERIC,AsOfdate TIMESTAMP,ticker VARCHAR);

这是可行的，但不方便。

还有其他方法可以返回动态列列表吗？

编辑以防止 SQL 注入，我通常拆分逗号分隔的列表并将其加入到系统视图中。不返回任何不是实际列名的内容。我最初没有提到这一点，只是为了让问题简短。

postgresql dynamic-sql

1 个回答

  1. Best Answer

    另一种方式，类似于我对您之前的问题提出的建议：返回一组众所周知的类型。由于您的列列表是动态的，因此为此目的创建一个临时表。这将向系统宣布类型。作为副作用，您会获得一个临时表以在会话期间保留结果 - 就像您在上一个问题中需要的那样。

    CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   'SELECT ' || colList || '
    FROM   prices
    WHERE  ...
    ORDER  BY ...';
END
$func$;

    称呼：

    CREATE TEMP TABLE t (col1 int, col2 date);
SELECT * FROM select_prices(NULL::t, 'col1, col2');

    或者，将结果保存在临时表中：

    INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2');

    如果您在同一会话中需要多个表，请使用序列来获取唯一名称。看：

    但是，这种方法（就像您问题中的其他两种方法一样）容易受到SQL 注入的影响。你需要确保它不会被滥用。

    同样，我会尝试改用这个简单的语句：

    CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;
    • 4

相关问题