有关PLPGSQL功能结果循环(FOR loop on PLpgSQL function resul

2019-10-22 20:14发布

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

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添加和我有错误):

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;

我们的想法是,我在数据库中的一些产品,其中有默认分类标识为1,然后在添加新类别,激活触发器其更新CategoryID (来自新加入的对象)的默认产品CategoryID -也许这听起来很愚蠢但我正在学习用Northwind数据库触发器,我不得不为自己创建的任务。 :)

但我不能保存它,因为错误的near get_products_by_category(1) 在PLPGSQL(我使用9.3版本)的任何可能性写的东西像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;

Answer 1:

基本上,你需要阅读手册本章内容: 遍历查询结果
而关于PLPGSQL触发功能 。

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;
...

这两个就像概念证明。 在大多数情况下,有蠢蠢欲动优越的基于集合的解决方案。 像这儿:

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

您可以像使用一组返流功能get_products_by_category(1)就像你在大多数情况下使用的表。

笔记

  • 你必须了解美元符:

    • 插入与PostgreSQL的单引号的文本
  • !=是有效的,太,而是使用SQL标准算<>

  • 表名称不是唯一的。 要确保你必须检查TG_TABLE_NAMETG_TABLE_SCHEMA

    • 如何检查如果一个表在一个给定的模式存在


文章来源: FOR loop on PLpgSQL function result