Re: Volatile functions in WITH - Mailing list pgsql-sql

From Sergey Konoplev
Subject Re: Volatile functions in WITH
Date
Msg-id CAL_0b1u6cRu==2x2U7taTLJBQxDsjs39YqyWGjOg_C-uZarfnQ@mail.gmail.com
Whole thread Raw
In response to Re: Volatile functions in WITH  (Ben Morrow <ben@morrow.me.uk>)
Responses Re: Volatile functions in WITH
List pgsql-sql
On Wed, Feb 20, 2013 at 10:16 AM, Ben Morrow <ben@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.ru@gmail.com



pgsql-sql by date:

Previous
From: Ben Morrow
Date:
Subject: Re: Volatile functions in WITH
Next
From: Ben Morrow
Date:
Subject: Re: Volatile functions in WITH