PostgreSQL: Pass table as argument in function
The parameter _source
in the MWE (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 plpgsql 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 AS
$func$
BEGIN
RETURN QUERY EXECUTE 'SELECT * FROM ' || _tbl;
END
$func$ LANGUAGE plpgsql;
SELECT * FROM process('source'); -- table name as string literal
See:
- Table name as a PostgreSQL function parameter
Or search for related questions and answers on site.
To make it work for any given table:
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!!
Detailed explanation:
- Refactor a PL/pgSQL function to return the output of various SELECT queries
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.