Problem calling Postgres function

Question

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.

Answer 1

Define the table name parameter as text and then pass a string:

create or replace function field_summaries(gap interval , _tbl text, _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; 
END;
$func$ 
language plpgsql
stable; 

Then call it like this:

select *
from field_summaries('5minutes', 'm_09', 'current');

• Hedi Hadhri: I get this error : select * from field_summaries('5minutes', 'm_09', 'current'); ERROR: function field_summaries(unknown, unknown, unknown) is not unique LINE 2: from field_summaries('5minutes', 'm_09', 'current'); ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts.
• user1822: You need to drop the old functoin first