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

编程入门行业动态更新时间:2024-10-27 10:25:55

本文介绍了如何在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.

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

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