0

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?

  • 2
  • Ugly, and is problematic for my use case, but thanks for the link! – Deven Phillips Jul 4 '16 at 4:53
  • PostgreSQL does not support table-valued parameters. You must pass a refcursor. – Craig Ringer Jul 4 '16 at 5:36
  • 1
    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. – Erwin Brandstetter Jul 5 '16 at 1:56
  • 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)) ; – Deven Phillips Jul 5 '16 at 4:12
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.