I wrote this function:
create or replace function field_summaries(gap interval , _tbl anyelement, _col text)
returns table(hour text, average numeric, minimum numeric, maximum numeric) as
$func$
begin
RETURN QUERY
EXECUTE format('select time_bucket($1, time)::text as hour,
avg(%I),
min(%I),
max(%I)
from %I d
where d in (select device_id from sensors)
group by hour', _col, _col, _col, _tbl)
USING gap; --<< this passes the parameter to the EXECUTE command
END;
$func$
language plpgsql
stable;
to which I pass a table name and a column name.I call it like this :
select field_summaries('5minutes', NULL::m_09, 'current');
which works fine. I need to call this function from my node.js code. so I need to pass a string as an argument. When I pass "Null::m_09" as a string I get an error. Is there a way to alter the function so that I can call it like this :
select field_summaries('5minutes', m_09, 'current');
Right now I get this when I do it:
ERROR: column "m_09" does not exist
LINE 1: select field_summaries('5minutes', m_09, 'current');
^
Any help is much appreciated.
Best Answer
Define the table name parameter as text and then pass a string:
Then call it like this: