CODEWITHSUNDEEP
postgresqlsyntaxcasting
Alexander McFarlane
Alexander McFarlane

Reputation: 11293

Postgresql function with values (from another table) as arguments

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"

Upvotes: 2

Views: 812

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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';

Upvotes: 2

Related Questions