Postgresql

還有其他方法可以選擇動態列列表嗎?

  • February 10, 2022

我需要讓我的使用者指定他們想要選擇的列列表。到目前為止,我知道實現這一目標的兩種方法。

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 注入,我通常拆分逗號分隔的列表並將其加入到系統視圖中。不返回任何不是實際列名的內容。我最初沒有提到這一點,只是為了讓問題簡短。

另一種方式,類似於我對您之前的問題提出的建議:返回一組眾所周知的類型。由於您的列列表是動態的,因此為此目的創建一個臨時表。這將向系統宣布類型。作為副作用,您會獲得一個臨時表以在會話期間保留結果 - 就像您在上一個問題中需要的那樣。

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;

引用自:https://dba.stackexchange.com/questions/53044