Problem with postgres Function

Question

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?

Answer 1

The reason for your error is the fact that you just concatenate the parameter into the string. When using EXECUTE, parameters should be denoted using $1, $2, ... inside the string and the values should be passed with the USING clause of the EXECUTE command.

Additionally, dynamic SQL should be created using the format() function. For one because it can properly deal with identifiers (using the %I placeholder). And it makes the SQL easier to read as well.

As the columns and their data types are fixed, I would also change the function to returns table(...) because that is easier to use later.

So the function should look something like this:

create or replace function field_summaries(gap interval , _tbl anyelement, _col text)                                
  returns table(hour text, average numeric, minimum numeric, maximum numeric) 
$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.device_id 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;

You will need to adjust the data types of the output columns in the returns table() part.

Answer 2

Change gap datatype from interval to text and enclose it between single quotes in your dynamic query.

create function test (gap text)
returns text
as $ftest$
declare
    cmd text;
begin
    cmd := 'select ''' ||  gap || '''::interval';
    return cmd;
end
$ftest$ language plpgsql 

select test('5 minute');

test                        
:--------------------------
select '5 minute'::interval

select '5 minute'::interval

| interval |
| :------- |
| 00:05:00 |

db<>fiddle here