Postgresql function with values (from another table) as arguments

Question

I can't figure out how to call a function with inputs specified from another table.

Let us assume the following function is being used to create a time interval:

create or replace function interval_generator(dt_start timestamp with TIME ZONE,
                                              dt_end timestamp with TIME ZONE,
                                              round_interval INTERVAL)
    returns TABLE(time_start timestamp with TIME ZONE,
                  time_end timestamp with TIME ZONE) as $$
BEGIN
return query
        SELECT
            (n)       time_start,
            (n + round_interval) time_end
        FROM generate_series(date_trunc('minute', dt_start), dt_end, round_interval) n;
END
$$
LANGUAGE 'plpgsql';

Let us create a dummy table for the minimal example:

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup 
as 
select *
from (
   VALUES 
    ('2017-08-17 04:00:00.000'::timestamp), 
    ('2017-08-17 05:00:00.000'::timestamp), 
    ('2017-08-18 06:00:00.000'::timestamp)
) as t (datetime);

Now my attempt is as follows:

select interval_generator(
    SELECT datetime FROM lookup Order By datetime limit 1,
    SELECT datetime FROM lookup Order By datetime Desc limit 1,
    '1 hours'::interval
);

and it just yields the generic error ERROR: syntax error at or near "SELECT"

Stefanov.sm · Accepted Answer · 2021-06-03 11:16:36Z

2

Enclose the SELECT statements in parentheses to make them expressions like this:

select * from interval_generator(
    (SELECT datetime FROM lookup Order By datetime limit 1),
    (SELECT datetime FROM lookup Order By datetime Desc limit 1),
    '1 hours'::interval
);

Please note that

SELECT datetime FROM lookup Order By datetime limit 1

is exactly

SELECT min(datetime) FROM lookup

which seems to me better readable. As the function body of interval_generator comprises of a single SQL query why don't you make it a plain SQL function instead of pl/pgsql?

<your-function-declaration> as $$
 SELECT
   (n) time_start,
   (n + round_interval) time_end
 FROM generate_series(date_trunc('minute', dt_start), dt_end, round_interval) n;
$$
LANGUAGE 'sql';

edited Jun 3, 2021 at 11:16

answered Jun 3, 2021 at 11:00

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql function with values (from another table) as arguments

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related