On Wed, Feb 20, 2013 at 10:16 AM, Ben Morrow <b...@morrow.me.uk> wrote: >> If you got mixed up with plpgsql anyway what is the reason of making >> this WITH query constructions instead of implementing everything in a >> plpgsql trigger on DELETE on exp then? > > I'm not sure what you mean. "exp" isn't a table, it's a WITH CTE. The
Sorry, I meant "item" of course, "exp" was a typo. > statement is deleting some entries from "item", and replacing some of > them with new entries, based on the information in the "item_expired" > view. I can't do anything with a trigger on "item", since there are > other circumstances where items are deleted that shouldn't trigger > replacement. Okay, I see. If the case is specific you can make a simple plpgsql function that will process it like FOR _row IN DELETE ... RETORNING * LOOP ... RETURN NEXT _row; END LOOP; > select * > from (select j.type, random() r from item j) i > where i.type = 1 > > the planner will transform it into > > select i.type, random() r > from item i > where i.type = 1 > > before planning, so even though random() is volatile it will only get > called for rows of item with type = 1. Yes, functions are executed depending on the resulting plan "A query using a volatile function will re-evaluate the function at every row where its value is needed". > I don't know if this happens, or may sometimes happen, or might happen > in the future, for rows eliminated because of DISTINCT. It is a good point. Nothing guarantees it in a perspective. Optimizer guarantees a stable result but not the way it is reached. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
