I wrote the following function to query data from tables and columns that I give as arguments:
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;
The problem is that when call the function like this:
select field_summaries('5minutes', NULL:: m_13, 'temperature');
I receive the following error:
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
Does anyone have an idea of what this could be?