1

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
asterix
  • 11
  • 7

2 Answers2

5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
2

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 ...
sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • wait a moment I understand but I am not expert in postgres a bit more help please – asterix Aug 13 '19 at 21:04
  • I would like you can write for me the correct format for this line: ( now() - interval '$1 day') AS date – asterix Aug 13 '19 at 21:08
  • @asterix: Replace `interval '$1 day'` with what I wrote to get for example `(now() - concat(days, ' day')::interval)`. – sticky bit Aug 13 '19 at 21:11