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
– wildplasser, Commented Apr 26, 2015 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
– Radek Anuszewski, Commented Apr 26, 2015 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
– wildplasser, Commented Apr 26, 2015 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
– Radek Anuszewski, Commented Apr 26, 2015 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
– wildplasser, Commented Apr 26, 2015 at 17:55

Community · Accepted Answer · 2017-05-23 11:51:05Z

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

Collectives™ on Stack Overflow

FOR loop on PLpgSQL function result

1 Answer 1

Notes

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Notes

Comments

Linked

Related