FOR loop on PLpgSQL function result

Question

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;

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;

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. :)

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))

Updated code:

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;

`IF TG_TABLE_NAME != 'Categories' THEN ...` Avoid the mixed case in names. Without quoting they will be folded to lowercase. (how was your `Categories` table defined ?) — wildplasser, Apr 26 '15 at 17:21
@wildplasser I corrected it (and also added forgotten END IF etc), but it still won't work, still it looks like there is mistake in `FOR product`, but I can't see anything wrong — Radek Anuszewski, Apr 26 '15 at 17:32
IIUC you don't need the loop. Just do a `UPDATE products SET CategoryID = NEW.id WHERE CategoryID = 1;` (BTW: 1 is an ugly sentinel value, IMHO) — wildplasser, Apr 26 '15 at 17:38
@wildplasser Hm... I am not a SQL pro, and now when I rethink it you are right I don't need even function which takes Products by Category ID :) And 1 is bad value, but this is not a production code - it is only for educational purposes (as all task- even if I don't need FOR and function, still I need task which includes function and FOR loop to learn it :) ) — Radek Anuszewski, Apr 26 '15 at 17:44
You can learn (and use) SQL without loops and cursors for many years. Once you need them, you'll notice... Triggers are a bit different, because some constraints cannot be expressed without them. (but in most cases this is an indication of a data-modelling problem) — wildplasser, Apr 26 '15 at 17:55

score 3 · Accepted Answer · edited May 23 '17 at 11:51

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

Or:

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

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

Notes

You must understand dollar-quoting:
- Insert text with single quotes in PostgreSQL
!= is valid, too, but rather use the SQL standard operator <>.
Table names are not unique. to be sure you must check TG_TABLE_NAME and TG_TABLE_SCHEMA.
- How to check if a table exists in a given schema

FOR loop on PLpgSQL function result

1 Answers1

Notes

Linked