Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?

Question

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?

• Michael Green: stackoverflow.com/questions/23008966/…
• Deven Phillips: Ugly, and is problematic for my use case, but thanks for the link!
• Craig Ringer: PostgreSQL does not support table-valued parameters. You must pass a refcursor.
• Erwin Brandstetter: Typically, there is a clean solution around the corner if you rethink your approach. Don't pass the resulting table to another function (which is not possible as such in Postgres - various alternatives exist), but use the table function in the FROM list of another query. The question would need to be more specific for a more specific answer.
• Deven Phillips: Yeah, that's kinda what I am doing, but I want to chain multiple funtuons deep to reuse functions. Ex: select * from fun1(fun2(param)) ;

Answer 1

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();

• user1822: A single query using distinct on () will be more efficient. select distinct on (id) id, data from device_data order by id, collected desc: rextester.com/WBYA88889