Postgresql
還有其他方法可以選擇動態列列表嗎?
我需要讓我的使用者指定他們想要選擇的列列表。到目前為止,我知道實現這一目標的兩種方法。
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;