Postgresql – Problem calling Postgres function

dynamic-sqlfunctionspostgresql-11

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:

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');