我写了一个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;