I am discovering TYPE
in PostgreSQL. I have a TABLE TYPE
that some table must respect (interface). For example:
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[]
);
I can create table using this template with:
CREATE TABLE test OF dataset;
I have seen many options in the API, but I am a little lost. I would like to know if it is possible to assign this type to function INPUT/OUTPUT
parameters.
Let say that I have a FUNCTION
called process
that receives a sample of records from a dataset TABLE
source
, processes them and then returns a TABLE
sink
with the same TYPE
.
That is I would like to know if it is possible to create a TYPE
that behaves like this:
CREATE FUNCTION process(
input dataset
) RETURNS dataset
AS ...
And that can be called like this:
SELECT
*
FROM
source, process(input := source) AS sink;
I wonder that it is possible with PostgreSQL, and ask how to do so. Does anyone of you know?
Here is a MWE of what I am trying to do:
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);
But it does not succeed, it is like source is perceived as a column instead of a SETOF RECORDS
with the type of dataset.
Best Answer
The parameter
_source
in the MWE (minimal working example) is not referenced anywhere. The identifiersource
in the function body has no leading underscore and is interpreted as constant table name independently.But it would not work like this anyway. SQL only allows to parameterize values in DML statements. See:
Solution
You can still make it work using dynamic SQL with
EXECUTE
in a plpgsql function:See:
Or search for related questions and answers on site.
To make it work for any given table:
Detailed explanation: