如何在Postgresql函数中使用变量进行循环查询

本文介绍了如何在Postgresql函数中使用变量进行循环查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在Postgresql(版本9.4.4)中有一个相当复杂的功能，需要一点帮助.

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

其中 ptable ， psymbol ， pstarttime 和 pendtime 是从函数调用传递的变量.

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.

当我尝试使用以上两种设置之一调用该函数时，我只是一片空白.任何帮助，将不胜感激.我找不到有关在for循环中使用变量的任何文档，因此可能无法实现.

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.

推荐答案

您需要带有 EXECUTE 的动态SQL-但仅才能参数化表名(或其他标识符)-无需参数化值.

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

并且不不要将参数值连接到查询中.这比必要的方法更昂贵且更容易出错.使用 使用 EXECUTE 的code>子句.

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;

或使用 format() :

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