PostgreSQL: Pass table as argument in function

Question

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.

Answer 1

The parameter _source in the minimal working example is not referenced anywhere. The identifier source 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:

• Error when setting n_distinct using a plpgsql variable

Solution

You can still make it work using dynamic SQL with EXECUTE in a PL/pgSQL function:

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
 LANGUAGE plpgsql AS
$func$
BEGIN
 RETURN QUERY EXECUTE 'SELECT * FROM ' || _tbl;
END
$func$;
SELECT * FROM process('source'); -- table name as string literal 

See:

• Table name as a PostgreSQL function parameter

Or search for related questions and answers. To make it work for any given table:

CREATE OR REPLACE FUNCTION process2(_tbl anyelement)
 RETURNS SETOF anyelement
 LANGUAGE plpgsql AS
$func$
BEGIN
 RETURN QUERY EXECUTE 'SELECT * FROM ' || pg_typeof(_tbl);
END
$func$;
SELECT * FROM process2(NULL::source); -- note the call syntax!

Detailed explanation:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

• jlandercy: Thanks for answering. Il will check it in few hours. Just to know before testing, is your solution accepting to receive rows from as SELECT. I mean SELECT * FROM process((SELECT * FROM source WHERE cond)).
• Erwin Brandstetter: @j: No, you pass a table name. There is no way to pass a table itself (no table variable). There are several ways around it. Related: stackoverflow.com/a/27853965/939860 or stackoverflow.com/a/31167928/939860. To work on the result of a query I would use a cursor or a temporary table ...

Answer 2

This will do what you want without needing any dynamic SQL:

drop table if exists source cascade;
drop function if exists process(dataset) cascade;
drop type if exists dataset cascade;
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)
;
create or replace function process(
 x_source dataset[]
)
returns setof dataset
as
$body$
select * from unnest(x_source);
$body$
language sql;
select *
from
 process(
 array(
 select
 row(id, t, x)::dataset
 from source
 )
 );

As far as I can tell (after googeling extensivly, because I had the same problem) you can't pass a table directly to a function.

However, as shown, you can transform a table into an array [] of a custom type that consists of several basic types (similar to a table definition).

Then you can pass that array and unnest it back into a table once you are in the function.