Postgresql – pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE

functionspostgresqluser-defined-type

I have a function which returns:

TABLE (id TEXT, collected TIMESTAMPTZ)

In another function, I would like to be able to pass the result of that first function as a parameter. For example:

CREATE FUNCTION my_func(devices TABLE(id TEXT, collected TIMESTAMPTZ)) ....

That syntax gives me an error:

ERROR: syntax error at or near "TABLE"

Is there some way to accomplish this in PostgreSQL?

Best Answer

OK, based on feedback from Erwin Brandstetter above, I came up with a VERY nice solution detailed below:

CREATE TABLE device_data (
  collected TIMESTAMPTZ,
  id VARCHAR(100),
  data JSON,
  PRIMARY KEY(id,collected)
);

COPY device_data (collected, id, data) FROM stdin;
2016-01-02 00:02:12+00  switch1.mycompany.com   {}
2016-01-02 00:02:12+00  switch2.mycompany.com   {}
2016-01-02 00:02:12+00  switch3.mycompany.com   {}
2016-01-02 00:02:12+00  switch4.mycompany.com   {}
2016-01-02 00:02:12+00  switch5.mycompany.com   {}
2016-01-02 00:02:12+00  switch6.mycompany.com   {}
2016-01-03 00:02:12+00  switch1.mycompany.com   {}
2016-01-03 00:02:12+00  switch3.mycompany.com   {}
2016-01-03 00:02:12+00  switch4.mycompany.com   {}
2016-01-03 00:02:12+00  switch5.mycompany.com   {}
2016-01-03 00:02:12+00  switch6.mycompany.com   {}
\.

CREATE OR REPLACE FUNCTION get_most_recent()
RETURNS TABLE(id TEXT, collected TIMESTAMPTZ) AS $$
SELECT
    id,
    MAX(collected) AS collected
FROM device_data GROUP BY id
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION get_data()
RETURNS TABLE(id TEXT, data JSON) AS $$
SELECT
  d.id,
  d.data
FROM device_data d
INNER JOIN get_most_recent() r ON r.id=d.id AND r.collected=d.collected
$$ LANGUAGE SQL;

SELECT * FROM get_data();

Related Solutions

Sql-server – Create Enumerated Function Parameter Type

You can't do what Microsoft does with built-ins, sorry. You will need to write your function to accept a string, for example, and evaluate it inline, e.g.

CREATE FUNCTION dbo.CustomDateDiff
(
  @datepart VARCHAR(32), -- does not need to be NVARCHAR
  @start    DATETIME,
  @end      DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN (SELECT CASE @datepart 
      WHEN 'HOUR'   THEN DATEDIFF(HOUR,   @start, @end) 
      WHEN 'MINUTE' THEN DATEDIFF(MINUTE, @start, @end)
      WHEN 'WEEK'   THEN DATEDIFF(WEEK,   @start, @end)
      ELSE               DATEDIFF(DAY,    @start, @end)
    END);
END
GO

Not that you can't use a conditional inside DATEDIFF - a lot of people think you could say:

DATEDIFF(@datepart,

DATEDIFF(CASE WHEN @datepart = 'HOUR' THEN HOUR END,

But neither of these will work - both yield:

Msg 1023, Level 15, State 1, Line 2
Invalid parameter 1 specified for datediff.

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

Best Answer

Related Solutions

Sql-server – Create Enumerated Function Parameter Type

PostgreSQL: Pass table as argument in function

Solution

Related Question