What is wrong in my dynamic query postgres statements inside function?

Question

I have a PL/pgSQL function that takes table name as dynamic parameter. As I am updating an existing query to take table name as dynamic parameter, this is what I have as my function:

DECLARE rec RECORD; 
BEGIN 
  EXECUTE 'insert into stat_300_8_0(ts, target, data)
               select distinct timestamp-(timestamp%3600) as wide_row_ts,
                      target, array[]::real[] as data
               from ' || temp_table_name || ' as temp 
               where class_id=8
                 and subclass_id=0 
                 and not exists (select ts from stat_300_8_0 
                                 where ts=temp.timestamp-(temp.timestamp%3600)
                                 and target=temp.target)'; 

  FOR rec IN EXECUTE 'SELECT DISTINCT timestamp AS ts 
                      FROM ' || temp_table_name ||
                    ' WHERE class_id=8' 
  LOOP 
      EXECUTE 'update stat_300_8_0 as disk_table
               set data[new_data.start_idx:new_data.end_idx] = array[data_0,data_1]
               from (select timestamp-(timestamp%3600) as wide_row_ts,
                            (timestamp%3600)/300 * 2 + 1 as start_idx,
                            ((timestamp%3600 / 300) + 1) * 2 as end_idx,
                            target, data_0, data_1
                     from ' || temp_table_name ||
                   ' where class_id=8 and subclass_id=0
                       and timestamp=rec.ts) as new_data
               where disk_table.ts=new_data.wide_row_ts
                 and disk_table.target=new_data.target';
  END LOOP; 
END;

However, when this function is executed I get an error saying

ERROR:  missing FROM-clause entry for table "rec"

However, rec is declared in the first line of the above code. I am not able to figure what is wrong with my queries. Any help is appreciated.

Note that "rec" is a record. Answer suggested by Eelke doesn't work in this case. — Alapati
– Alapati, Commented Aug 13, 2016 at 6:34
Please always provide a complete function include the header (and footer). Function parameters are visible inside the body, and we need to see all modifiers being used. — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 13, 2016 at 11:54

Chris Travers · Accepted Answer · 2016-08-13 06:30:31Z

2

Supplemental to Eelke's answer:

Assuming temp_table_name is an argument, you really, really want to run it through quote_ident() because otherwise someone could create a table with a name that could inject sql into your function.
Instead of the change suggested there, you are better off using EXECUTE...USING since that gives you parameterization regarding values (and hence protection against SQL injection). You would change rec.ts to $1 and then add to the end USING ts.rec (outside the quoted execute string). This gives you a parameterized statement inside your execute which is safer. However parameters cannot include table names, so it doesn't spare you from the first point above.

answered Aug 13, 2016 at 6:30

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

What is wrong in my dynamic query postgres statements inside function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related