Eu escrevi a seguinte função para consultar dados de tabelas e colunas que dou como argumentos:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns SETOF anyelement as
$func$
BEGIN
RETURN QUERY EXECUTE
'select
time_bucket(' || gap || ', time)::text as hour,
avg(' || _col ||'),
min(' || _col ||'),
max(' || _col ||')
from ' || pg_typeof(_tbl) || ' d
where d.device_id in (
select device_id from sensors)
group by hour';
END
$func$ language sql stable;
O problema é que ao chamar a função assim:
select field_summaries('5minutes', NULL:: m_13, 'temperature');
Eu recebo o seguinte erro:
ERROR: syntax error at or near ":"
LINE 2: time_bucket(00:05:00, time)::text as hour, ...
^
QUERY: select
time_bucket(00:05:00, time)::text as hour,
avg(temperature),
min(temperature),
max(temperature)
from m_13 d
where d.device_id in (
select device_id from sensors)
group by hour
CONTEXT: PL/pgSQL function field_summaries(interval,anyelement,text) line 3 at RETURN QUERY
Alguém tem ideia do que pode ser isso?