Postgresql

PostgreSQL:將表作為參數傳遞給函式

  • June 27, 2020

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數據集類型。

_sourceMWE(最小工作範例)中的參數未在任何地方引用。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!!

詳細解釋:

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