关键词：
【中文标题】如何在 PL/pgSQL 中的动态选择查询中使用迭代器变量？【英文标题】：How to work with a iterator variable on a dynamic select query in PL/pgSQL? 【发布时间】：2014-11-18 11:10:36 【问题描述】：我需要在 PostgreSQL 9.1 中提供现金流量报告。
在余额行 (BalanceLine) 中，一个函数 (getBalanceLine) 负责获取银行账户的总和及其贷方或借方头寸。
数字行 (num_row) 需要减少到零，以向函数 getBalanceLine 指示临时表必须是 DROPed。该临时表将平衡线结果保存为计算的“工作内存”。
问题是函数getBalanceLine 上的num_row 变量没有按照我的LOOP 子句中的说明递减。
以下是PL/pgSQL函数：
DROP FUNCTION IF EXISTS report_cash_flow();
CREATE FUNCTION report_cash_flow() RETURNS TABLE(Date date, Company varchar(128), Bank varchar(64), Partner varchar(128), Document varchar(64), Credit numeric, Debit numeric, Line integer, BalanceLine numeric) AS
$BODY$
DECLARE
num_row int := 0;
BEGIN
num_row = ( SELECT
COUNT(*)
FROM
account_move_line aml
INNER JOIN res_company rc ON rc.id = aml.company_id
INNER JOIN res_partner rp ON rp.id = aml.partner_id
INNER JOIN account_journal aj ON aj.id = aml.journal_id
INNER JOIN account_account aa ON aa.id = aml.account_id
WHERE
aml.state = 'valid'
AND aml.reconcile_id IS NULL );
FOR Date, Company, Bank, Partner, Document, Credit, Debit, Line, BalanceLine IN
SELECT
aml.date_maturity AS Date,
rc.name AS Company,
aj.name AS Bank,
rp.name AS Partner,
aml.name AS Document,
aml.credit AS Credit,
aml.debit AS Debit,
num_row AS Line,
getBalanceLine(aml.credit, aml.debit, num_row) AS BalanceLine
FROM
account_move_line aml
INNER JOIN res_company rc ON rc.id = aml.company_id
INNER JOIN res_partner rp ON rp.id = aml.partner_id
INNER JOIN account_journal aj ON aj.id = aml.journal_id
INNER JOIN account_account aa ON aa.id = aml.account_id
WHERE
aml.state = 'valid'
AND aml.reconcile_id IS NULL
ORDER BY
Document
LOOP
num_row := num_row - 1;
RAISE NOTICE '%', num_row;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql';
SELECT * FROM report_cash_flow();
查询结果如下：
date company bank partner document credit debit line balanceline
01/10/2013 Company 1 Bank 1 Partner 1 00003621/1 0.00 520.56 4 1.024,00
01/10/2013 Company 1 Bank 2 Partner 2 00003622/1 32.00 0.00 4 922,00
09/10/2014 Company 1 Bank 1 Partner 3 00003623/1 0.00 18009.65 4 -17.087,65
10/10/2014 Company 1 Bank 2 Partner 4 00003624/1 6126.95 0.00 4 -10.960,70
以下是RAISE NOTICE '%', num_row的结果：
NOTICE: 4
NOTICE: 3
NOTICE: 2
NOTICE: 1
【问题讨论】：
【参考方案1】：为您的FOR 循环提供行的查询在循环的第一次迭代之前执行一次。您必须在循环中调用函数 getBalanceLine()，而不是在基本查询中。
但是，您的整个方法是不必要的冗长和昂贵的。
简化，步骤 1
CREATE FUNCTION report_cash_flow()
RETURNS TABLE(Date date, Company text, Bank text, Partner text, Document text, Credit numeric, Debit numeric, Line integer, BalanceLine numeric) AS
$func$
DECLARE
_iterator int := 0;
BEGIN
FOR Date, Company, Bank, Partner, Document, Credit, Debit, Line IN
SELECT aml.date_maturity AS Date -- alias useless
rc.name AS Company,
aj.name AS Bank,
rp.name AS Partner,
aml.name AS Document,
aml.credit AS Credit,
aml.debit AS Debit,
count(*) OVER () AS Line -- = initial num_row
FROM account_move_line aml
JOIN res_company rc ON rc.id = aml.company_id
JOIN res_partner rp ON rp.id = aml.partner_id
JOIN account_journal aj ON aj.id = aml.journal_id
JOIN account_account aa ON aa.id = aml.account_id
WHERE aml.state = 'valid'
AND aml.reconcile_id IS NULL
ORDER BY Document
LOOP
BalanceLine := getBalanceLine(Credit, Debit, Line - _iterator);
RETURN NEXT;
_iterator := _iterator + 1;
RAISE NOTICE '%', Line - _iterator;
END LOOP;
RETURN;
END
$func$ LANGUAGE plpgsql;
SELECT 中执行此操作。
引入一个额外的变量来计数循环，在我的例子中是_iterator。
我在变量前面加上 _ 以避免命名冲突。
不要引用语言名称plpgsql，它是一个标识符。
简化，第 2 步
您或许可以将这个简单的查询与window functions 一起使用：
SELECT aml.date_maturity AS Date
, rc.name AS Company
, aj.name AS Bank
, rp.name AS Partner
, aml.name AS Document
, aml.credit
, aml.debit
, count(*) OVER () AS Line -- or the decreasing number?
, getBalanceLine(aml.credit
, aml.debit
, count(*) OVER () + 1 -
row_number() OVER (ORDER BY Document)) AS BalanceLine
FROM account_move_line aml
JOIN res_company rc ON rc.id = aml.company_id
JOIN res_partner rp ON rp.id = aml.partner_id
JOIN account_journal aj ON aj.id = aml.journal_id
JOIN account_account aa ON aa.id = aml.account_id
WHERE aml.state = 'valid'
AND aml.reconcile_id IS NULL
ORDER BY Document;
或者使用更复杂的：
count(*) OVER (ORDER BY Document
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
更多解释：
How to use a ring data structure in window functions【讨论】：
Erwin 我非常感谢您的帮助。诀窍是（在步骤 1 中）在 LOOP 子句上操作 BalanceLine。谢谢大家的解释。PL/pgSQL 代码的问题
...标，它将保存我的动态查询的输出，然后我在另一个查询中的循环中使用该值以使我的输出显示在屏幕上。我 查看详情
如何使用 pl/pgsql 将具有动态元素名称的 JSON 数据转换为行？
】如何使用pl/pgsql将具有动态元素名称的JSON数据转换为行？【英文标题】：HowtoconvertJSONdatawithdynamicelementnamesintorowsusingpl/pgsql?【发布时间】：2018-06-1317:10:59【问题描述】：我需要从pl/pgsql函数中的restful服务获取json。（我无法控制... 查看详情
如何在 PL/pgSQL 中获取动态生成的字段名称的值
】如何在PL/pgSQL中获取动态生成的字段名称的值【英文标题】：HowtogetthevalueofadynamicallygeneratedfieldnameinPL/pgSQL【发布时间】：2016-10-0115:06:23【问题描述】：示例代码精简了演示问题的基本要素：CREATEORREPLACEFUNCTIONmytest4()RETURNSTEXTAS$$D... 查看详情
在 PL/PGSQL 动态 SQL 内部函数中引用局部变量
】在PL/PGSQL动态SQL内部函数中引用局部变量【英文标题】：ReferenceLocalVariableInPL/PGSQLDynamicSQLInsideFunction【发布时间】：2015-06-1015:33:28【问题描述】：我有一个用于数据处理的PL/PGSQL函数。我需要先从表中选择每一行，然后检索每列... 查看详情
在 PL/pgSQL 函数中使用变量
...您可以在PL/pgSQL函数只需编写命令即可。出现在命令文本中的任何PL/pgSQL变量名都被视为一个参数，然后将变量的当前值提供为运行 查看详情
如何在 PL/pgSQL 函数中使用 WITH 和 IF
】如何在PL/pgSQL函数中使用WITH和IF【英文标题】：HowcanIuseWITHtogetherwithIFinPL/pgSQLfunction【发布时间】：2021-01-0823:24:53【问题描述】：CREATEORREPLACEFUNCTIONtest()RETURNSbooleansecuritydefinerAS$BODY$BEGINwithzzzas(selectfoofromdata)IFEXISTS(sel 查看详情
PL/pgSQL 从表中动态复制数据
...问题描述】：我正在尝试编写一些SQL来根据information_schema中的内容从给定数据库中的所有PostgreSQL表中复制数据。它应该将数据文件输出到我的本地机器，准备好导入另一台机器。最终，我将对此进行调整，以便仅转储表的选定... 查看详情
PostgreSQL PL/pgSQL：存储在表中的查询（营业时间）
】PostgreSQLPL/pgSQL：存储在表中的查询（营业时间）【英文标题】：PostgreSQLPL/pgSQL:querystoredwithinatable(openinghours)【发布时间】：2018-07-2314:11:28【问题描述】：我有一个应用程序（postgresql9.6将在10上迁移），我想在其中检索表中的结... 查看详情
如何从 C++ 代码调用 PL/pgSQL 函数
】如何从C++代码调用PL/pgSQL函数【英文标题】：HowCanICallPL/pgSQLFunctionFromC++Code【发布时间】：2012-11-2013:13:09【问题描述】：我正在尝试使用PL/pgSQL调用在PostgreSQL中声明的函数。为此，我编写了下面的代码。我的功能正在运行，但... 查看详情
如何在 PL/pgSQL 中按行类型返回表
】如何在PL/pgSQL中按行类型返回表【英文标题】：HowtoreturnatablebyrowtypeinPL/pgSQL【发布时间】：2014-12-2518:59:07【问题描述】：我正在尝试使用PL/pgSQL(PostgreSQL9.3)实现一个函数，该函数返回一个与参数中的输入表具有相同结构的表。... 查看详情
PL/pgSQL 函数中的可选参数
】PL/pgSQL函数中的可选参数【英文标题】：OptionalargumentinPL/pgSQLfunction【发布时间】：2012-07-1604:36:39【问题描述】：我正在尝试编写一个带有可选参数的PL/pgSQL函数。它基于过滤的记录集（如果指定）执行查询，否则对表中的整个... 查看详情
PL/pgSQL 函数 - 遍历特定列并在循环中执行第二个查询
】PL/pgSQL函数-遍历特定列并在循环中执行第二个查询【英文标题】：PL/pgSQLfunctions-loopthroughspecificcolumnandexecutesecondqueryinloop【发布时间】：2014-03-0914:16:13【问题描述】：正如您在标题中看到的，我想遍历保存建筑物ID的特定列，然... 查看详情
从 PL/pgSQL 函数中的 INSERT INTO 访问和返回结果
】从PL/pgSQL函数中的INSERTINTO访问和返回结果【英文标题】：AccessandreturnresultfromINSERTINTOinPL/pgSQLfunction【发布时间】：2017-03-2200:18:22【问题描述】：我目前正在学习很多PostgreSQL，尤其是PLPGSQL，并且在处理函数中的查询结果方面遇... 查看详情
在 PL/pgSQL 块中运行 SELECT
...】：我是PostgreSQL的新手，我必须在s-s-rS报告中使用内联查询来从PostgreSQL数据库中获取数据。场景是：根据报告参数的选定值，我需要从不同的表中获取输出。请参阅下面的示例内联查询。DO$do$BEGINIF($1=\'Monthly\')THEN 查看详情
PL/pgSQL 函数在 pgAdmin 之外无法正确运行
...创建了一个PL/pgSQL函数。它返回一个查询并更新同一个表中的条目。在pgAdmin中调用，它按预期运行。从外部代码运行时，会返回表，但不会运行更新。想知道这 查看详情
PL/pgSQL 中的光标
】PL/pgSQL中的光标【英文标题】：CursorinPL/pgSQL【发布时间】：2016-06-0412:32:52【问题描述】：我想在函数中使用游标，但我在定义一个变量时出错，请查看并帮助:)CREATEORREPLACEFUNCTIONcurson_func(start_datetimestamp,end_datetimestamp)RETURNSSETOFint... 查看详情
如何在函数的 UPDATE 或 SELECT 语句中使用动态列名？
】如何在函数的UPDATE或SELECT语句中使用动态列名？【英文标题】：HowtousedynamiccolumnnamesinanUPDATEorSELECTstatementinafunction?【发布时间】：2015-08-2422:42:10【问题描述】：在PostgreSQL9.1PL/pgSQL中，给定一个查询：selectfk_list.relnamefrom...其中rel... 查看详情
执行 SELECT 语句并丢弃 PL/pgSQL 中的结果
】执行SELECT语句并丢弃PL/pgSQL中的结果【英文标题】：ExecuteaSELECTstatementanddiscardresultsinPL/pgSQL【发布时间】：2012-03-1210:03:11【问题描述】：我正在尝试在PostgreSQL中创建一个简单的存储过程，我想在其中执行一个SELECT语句。我不想... 查看详情