CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsql
asterix
asterix

Reputation: 11

How to pass the number of days to a Postgres function?

Argument days in function getAvgByDay() doesn't work, I guess because it is inside quotes:

CREATE OR REPLACE FUNCTION getAvgByDay(days int)
RETURNS TABLE ( average text,
                date timestamp with time zone
               ) AS
$func$
BEGIN
RETURN QUERY
SELECT to_char( AVG(measure), '99999D22') AS average, ( now() - interval '$1 day') AS date
FROM (
        SELECT mes.date, mes.measure
        FROM measures mes
        WHERE mes.date < ( now() - interval '$1 day')
    ) AS mydata;
END
$func$ 
LANGUAGE plpgsql;

Upvotes: 1

Views: 1380

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659247

Assuming the column measures.date is actually data type timestamptz and not a date:

CREATE OR REPLACE FUNCTION get_avg_by_day(_days int)
  RETURNS TABLE (average text, ts timestamptz) AS  -- not using "date" for a timestamp
$func$
SELECT to_char(avg(measure), '99999D22') -- AS average
     , now() - interval '1 day' * $1     -- AS ts
FROM   measures m
WHERE  m.date < now() - interval '1 day' * $1
$func$  LANGUAGE sql;
  • No need for PLpgSQL, can be a simper SQL function.
  • No need for a subquery. Only adds complexity and cost for no gain.
  • No need for column aliases in the outer query level. Those are not used, as visible column names are defined in the RETURNS clause.
  • No need for extra parentheses. Operator precedence works as desired anyway. (No harm in this case, either.)
  • Don't use CaMeL case identifier in Postgres if you can avoid it.
  • Don't call a timestamptz column "date". That's misleading. Using "ts" instead.

  • Most importantly: You suspected as much, and "sticky bit" already explained: no interpolation inside strings. But just multiply the time unit with your integer input to subtract the given number of days:

    interval '1 day' * $1

    That's faster and cleaner than string concatenation.

Upvotes: 5

sticky bit
sticky bit

Reputation: 37507

There's no interpolation in strings. But you can concatenate strings and cast them to an interval. Try:

... concat(days, ' day')::interval ...

Or you could use format(), that's probably a little closer to what you originally had:

... format('%s day', days)::interval ...

Upvotes: 2

Related Questions