0

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.

0

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');
2
  • 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. Mar 14 at 17:48
  • You need to drop the old functoin first Mar 14 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.