Postgresql
PostgreSQL:將表作為參數傳遞給函式
我
TYPE
在 PostgreSQL 中發現。我有一個TABLE TYPE
某些表必須尊重的(介面)。例如:CREATE TYPE dataset AS( ChannelId INTEGER ,GranulityIdIn INTEGER ,GranulityId INTEGER ,TimeValue TIMESTAMP ,FloatValue FLOAT ,Status BIGINT ,QualityCodeId INTEGER ,DataArray FLOAT[] ,DataCount BIGINT ,Performance FLOAT ,StepCount INTEGER ,TableRegClass regclass ,Tags TEXT[] ,WeightedMean FLOAT ,MeanData FLOAT ,StdData FLOAT ,MinData FLOAT ,MaxData FLOAT ,MedianData FLOAT ,Percentiles FLOAT[] );
我可以使用此模板創建表格:
CREATE TABLE test OF dataset;
我在API中看到了很多選項,但我有點迷茫。我想知道是否可以將此類型分配給函式
INPUT/OUTPUT
參數。假設我有一個
FUNCTION
呼叫process
,它從數據集中接收記錄樣本TABLE
source
,處理它們,然後返回一個TABLE
sink
相同的TYPE
。那就是我想知道是否可以創建一個
TYPE
行為如下:CREATE FUNCTION process( input dataset ) RETURNS dataset AS ...
這可以這樣稱呼:
SELECT * FROM source, process(input := source) AS sink;
我想知道 PostgreSQL 是否可行,並詢問如何做到這一點。你們有誰知道嗎?
這是我正在嘗試做的 MWE:
DROP TABLE IF EXISTS source; DROP FUNCTION IF EXISTS process(dataset); DROP TYPE dataset; CREATE TYPE dataset AS ( id INTEGER ,t TIMESTAMP ,x FLOAT ); CREATE TABLE source OF dataset; ALTER TABLE source ADD PRIMARY KEY(Id); INSERT INTO source VALUES (1, '2016-01-01 00:00:00', 10.0) ,(2, '2016-01-01 00:30:00', 11.0) ,(3, '2016-01-01 01:00:00', 12.0) ,(4, '2016-01-01 01:30:00', 9.0) ; CREATE OR REPLACE FUNCTION process( _source dataset ) RETURNS SETOF dataset AS $BODY$ SELECT * FROM source; $BODY$ LANGUAGE SQL; SELECT * FROM process(source);
但它沒有成功,就像源被視為列而不是
SETOF RECORDS
數據集類型。
_source
MWE(最小工作範例)中的參數未在任何地方引用。source
函式體中的標識符沒有前導下劃線,獨立解釋為常量表名。但無論如何它不會像這樣工作。SQL 只允許參數化DML 語句中的*值。*看:
解決方案
EXECUTE
您仍然可以在 plpgsql 函式中使用動態 SQL 使其工作:CREATE TYPE dataset AS (id integer, t timestamp, x float); CREATE TABLE source OF dataset (PRIMARY KEY(Id)); -- add constraints in same command INSERT INTO source VALUES (1, '2016-01-01 00:00:00', 10.0) ,(2, '2016-01-01 00:30:00', 11.0); CREATE OR REPLACE FUNCTION process(_tbl regclass) RETURNS SETOF dataset AS $func$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM ' || _tbl; END $func$ LANGUAGE plpgsql; SELECT * FROM process('source'); -- table name as string literal
看:
或在網站上搜尋相關問題和答案。
要使其適用於任何給定的表:
CREATE OR REPLACE FUNCTION process2(_tbl anyelement) RETURNS SETOF anyelement AS $func$ BEGIN RETURN QUERY EXECUTE 'SELECT * FROM ' || pg_typeof(_tbl); END $func$ LANGUAGE plpgsql; SELECT * FROM process2(NULL::source); -- note the call syntax!!
詳細解釋: