PLpgSQL函数结果上的FOR循环 [英] FOR loop on PLpgSQL function result

问题描述

我写了一个PLpgSQL函数，该函数应该返回SETOF products表:

I wrote a PLpgSQL function which should return SETOF products table:

CREATE OR REPLACE FUNCTION get_products_by_category
(selected_category_id smallint DEFAULT 1) RETURNS SETOF products AS
$BODY

$BEGIN
RETURN QUERY  (SELECT * FROM products WHERE CategoryID = selected_category_id);
END;

$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100
ROWS 1000;

接下来，我想遍历另一个函数中的结果(未完成的视图，因为我尝试在PgAdmin III中添加并且出现错误):

And next I want to iterate over results in another function (non-finished view, because I try to add in PgAdmin III and I have errors):

DECLARE
    R RECORD;
BEGIN
IF TG_TABLE_NAME != 'Categories' THEN
    RAISE 'This trigger function is for Categories, but was called for %', TG_TABLE_NAME;
FOR R IN get_products_by_category(1) LOOP
    UPDATE products SET CategoryID = NEW.id WHERE id = R.id;
RETURN NEW;

这个想法是我在数据库中有一些产品，这些产品的默认类别ID为1.然后，当添加新的类别时，会触发触发器，该触发器会使用默认CategoryID-听起来听起来很愚蠢，但我正在使用Northwind数据库学习触发器，因此必须自己创建任务. :)

The idea is that I have some Products in database, which have default Category ID as 1. And then, when new Category is added, trigger is fired which update CategoryID (from freshly added object) for Products with default CategoryID - maybe it sounds stupid but I am learning triggers with Northwind Database and I had to create task for myself. :)

但是由于错误near get_products_by_category(1)，我无法保存它. 在PLpgSQL(我正在使用9.3版本)中，是否有可能在Java中编写类似的内容:

But I can't save it, because of errors near get_products_by_category(1). Is in PLpgSQL (I am using 9.3 version) any possibility to write something like in Java:

for (Product product: dao.getProductsByCategory(categoryId))

更新的代码:

DECLARE
    selected_products products[];
    product products;
BEGIN
IF TG_TABLE_NAME != 'categories' THEN
    RAISE 'This trigger function is for Categories, but was called for %', TG_TABLE_NAME;
END IF;
selected_products := get_products_by_category(1);
FOR product IN selected_products LOOP
    UPDATE products SET CategoryID = NEW.id
        WHERE id = R.id;
END LOOP;
RETURN NEW;
END;

推荐答案

基本上，您需要阅读手册中的这一章: 遍历查询结果 .
关于 plpgsql触发函数.

Basically, you need to read this chapter in the manual: Looping Through Query Results.
And about plpgsql trigger functions.

CREATE OR REPLACE FUNCTION my_trigger_func()
   RETURNS trigger AS
$func$
DECLARE
    _prod products;
BEGIN
   IF TG_TABLE_NAME <> 'categories' THEN
      RAISE EXCEPTION 'Trigger func for "categories", not for %', TG_TABLE_NAME;
   END IF;

   FOR _prod IN 
      SELECT * FROM get_products_by_category(1)
   LOOP
      UPDATE products p
      SET    categoryid = NEW.id
      WHERE  p.id = _prod.id;
   END LOOP;
   RETURN NEW;
END
$func$  LANGUAGE plpgsql

或者:

...
DECLARE
    _id int;
BEGIN
   ...
   FOR _id IN 
      SELECT id FROM get_products_by_category(1)
   LOOP
      ...
      WHERE  p.id = _id;
...

两者都是概念的证明.大多数情况下，都会有一个基于集合的高级解决方案.就像这里:

Both just as proof of concept. Most of the time, there is a superior set-based solution around the corner. Like here:

UPDATE products p
SET    categoryid = NEW.id
FROM   get_products_by_category(1) x
WHERE  p.id = x.id;

您可以使用像get_products_by_category(1)这样的set-returning函数，就像在大多数情况下使用表一样.

You can use a set-returning function like get_products_by_category(1) just like you would use a table in most contexts.

• 您必须了解美元报价:

• You must understand dollar-quoting:

• 在PostgreSQL中插入带单引号的文本

!=也是有效的，而是使用SQL标准运算符<>.

!= is valid, too, but rather use the SQL standard operator <>.

表名不是唯一的.确保必须检查TG_TABLE_NAME和TG_TABLE_SCHEMA.

Table names are not unique. to be sure you must check TG_TABLE_NAME and TG_TABLE_SCHEMA.

• 如何检查如果表存在于给定的模式中

这篇关于PLpgSQL函数结果上的FOR循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！