0

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?

2
  • sorry it's func$ language plpgsql Mar 13 at 7:38
  • Hi, welcome to the forum. Please tag your PostgreSQL version. Is seems related to TimescaleDB, change gap datatype from interval to text enclosed in a single quotes.
    – McNets
    Mar 13 at 8:16
0

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.

3
  • Hi, thanks for your answer. I have another question: Could I call the function using a text as the table name instead of NULL::tablename ? I need to enter the name as string when I call the function but when I do it it says table does not exist. Thanks in advance. Mar 14 at 14:43
  • Sure. That's why using format() is recommended. Mar 14 at 14:58
  • It seems to not work =/ For example when I call the function like this 'select fied_summaries('5minutes', NULL:: m_13, 'current')' it executes but when I do 'select fied_summaries('5minutes', 'm_13', 'current')' it throws an error that m_13 does not exist. My goal is to call the function like that. Is there away to make the NULL::m_13 call inside the function? Mar 14 at 15:08
1

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

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.