How do I use variables in a postgresql function for loop query

Question

I have a rather complicated function in postgresql (Version 9.4.4) that I need a bit of help with.

I have a loop (with lots of work below) declared like this inside of my function:

CREATE OR REPLACE function getRSI(
    psymbol varchar,
    pstarttime timestamp with time zone,
    pendtime timestamp with time zone,
    pduration double precision,
    ptable varchar
    )
 RETURNS SETOF rsi AS
 $BODY$
 declare
    row_data record;
    -- some variables
 begin
    FOR row_data IN SELECT datetime, value FROM "4" WHERE symbol = 'AAPL' 
    AND datetime BETWEEN '2015-11-23 09:30:00 -0500' AND 
    '2015-11-23 15:59:59-0500' LOOP
       -- enter code here
    END LOOP;
 end
 $BODY$ LANGUAGE plpgsql

This works perfectly, I can get the result of my function and have it crunch all the numbers for me.

I would like to get the loop to work like this:

FOR row_data in select datetime, value from quote_ident(ptable) 
where symbol = quote_literal(psymbol) and datetime 
between quote_literal(pstarttime) AND quote_literal(pendtime) LOOP

where ptable, psymbol, pstarttime and pendtime are variables passed from the function call.

But I would be happy having to hardcode a table and getting the other three things to be based on a variable:

FOR row_data in select datetime, value from "4" where symbol =
quote_literal(psymbol) and datetime between quote_literal(pstarttime)
AND quote_literal(pendtime) LOOP

Yes, I know that I have a table named after a number, nothing I can do about it in my current setup.

When I try to call the function with either of the above setups, I just get a blank. Any help would be appreciated. I can't find any documentation about using a variable in a for loop so it might not be possible.

You should provide a complete (minimal) function. The complete header and declarations matter. And always your version of Postgres. — Erwin Brandstetter, Dec 30 '15 at 09:23
@Erwin appreciate that feedback, I updated the question even though you already perfectly answered it for me. Thank you! — Ben Hernandez, Dec 30 '15 at 09:35
`row_data` should be declared as `record`. I took the liberty to round it off. — Erwin Brandstetter, Dec 30 '15 at 09:44
I didn't realize that I could use a record. I had created a specific type for the results of the query based on another stack example of a loop I had found. Much more readable using the record. Thanks (again!) — Ben Hernandez, Dec 30 '15 at 09:49

Erwin Brandstetter · Accepted Answer · 2018-01-23T22:18:17.790

You need dynamic SQL with EXECUTE - but only to parameterize the table name (or other identifiers) - not necessary to parameterize values.

And do not concatenate parameter values into the query. That's more expensive and error prone than necessary. Use the USING clause of EXECUTE instead.

FOR row_data IN
   EXECUTE '
    SELECT datetime, value FROM ' || quote_ident(ptable) || '
    WHERE  symbol = $1
    AND    datetime between $2 AND $3'
   USING psymbol, pstarttime, pendtime
LOOP
  -- do stuff
END LOOP;

Or use format():

   EXECUTE format('
    SELECT datetime, value FROM %I 
    WHERE  symbol = $1
    AND    datetime between $2 AND $3', ptable)
   USING psymbol, pstarttime, pendtime

@Erwin, in the above line, shouldn't we also add concatenation pipes right before the quote_ident part, like this?: SELECT datetime, value FROM ' || quote_ident(ptable) || ' — Sébastien Clément, Jan 23 '18 at 17:18

Sathish · Answer 2 · 2015-12-30T09:20:55.347

1

change your for loop like this

FOR row_data in execute 'select datetime, value from "4" where symbol =' || 
quote_literal(psymbol)  || 'and datetime between' || quote_literal(pstarttime)
|| 'AND ' || quote_literal(pendtime) LOOP

edited Dec 30 '15 at 09:20

answered Dec 30 '15 at 09:10

Sathish

4,419
4
30
59

You are my new favorite person! Aside from changing the end from `quote_literal(pendtime) || 'LOOP'` to `quote_literal(pendtime) LOOP` that worked perfectly! I spent like an hour googling how to do that before I finally gave up and asked. – Ben Hernandez Dec 30 '15 at 09:16
Thanks... @BenHernandez – Sathish Dec 30 '15 at 09:19

How do I use variables in a postgresql function for loop query

2 Answers2

Linked