我在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相关:
- 有关记录的手册类型
- 表名作为PostgreSQL函数参数
- plpgsql-在声明语句中使用动态表名
更多推荐
如何在Postgresql函数中使用变量进行循环查询
发布评论