Integrating resultant columns from function to main query

Question

I have created a function that returned a table with one row and three columns.

DROP FUNCTION IF EXISTS get_event_deposit_values(INTEGER, date, date);
CREATE OR REPLACE FUNCTION get_event_deposit_values(event_id INTEGER, start_date date, end_date date)
RETURNS TABLE (carried_over INTEGER, current_month INTEGER, deposit INTEGER)
AS $$
DECLARE status INTEGER;
BEGIN
    carried_over := 0;
    current_month := 0;
    deposit := 0;

    RETURN QUERY
    SELECT carried_over, current_month, deposit;
END
$$ LANGUAGE plpgsql;

Run a simple query in pgAdmin.
select * FROM get_event_deposit_values(20170913, '1999/01/01', '2018/05/11');
It returns three columns with one row.

Then, run a query in conjunction with main query (saw it on a website).
select t.id from t_events AS t, lateral get_event_deposit_values(t.id, '1999/01/01', '2018/05/11') where id = 20170913;

An error occurred near get_event_deposit_values(t.id. The PostgreSQL I am running is of version 8.3 which is pretty outdated. Is there any alternative way of doing this?

score 1 · Accepted Answer · answered May 12 '18 at 04:19

The documentation on lateral subqueries (in the first version, that supports it, which is 9.3, if I haven't overlooked something) state, that it was default for table functions to be lateral even without the keyword. So there might be a slight chance, that this also the case in your version.

So try what happens, if you just remove the keyword LATERAL.

If that doesn't work (which I'd expect) you can select each of the different columns of the function in a subquery in the column list as a work around.

SELECT t.id,
       (SELECT carried_over
               FROM get_event_deposit_values(t.id, '1999/01/01', '2018/05/11')) carried_over,
       (SELECT current_month
               FROM get_event_deposit_values(t.id, '1999/01/01', '2018/05/11')) current_month,
       (SELECT deposit
               FROM get_event_deposit_values(t.id, '1999/01/01', '2018/05/11')) deposit
       FROM t_events t
       WHERE t.id = 20170913;

(And possibly consider updating to a more recent version.)

Seems like it is the only way. Thus, I split the function into three smaller ones which return only a value rather than a table cause I think it might be more efficient. — M. Ko, May 14 '18 at 03:14

Integrating resultant columns from function to main query

1 Answers1