Re: BUG #14301: function in case expression called when it should not be

On Mon, Aug 29, 2016 at 03:14:33PM +0000, rikard(at)ngs(dot)hr wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14301
> Logged by: Rikard Pavelic
> Email address: rikard(at)ngs(dot)hr
> PostgreSQL version: 9.4.9
> Operating system: CentOS
> Description:
>
> I was investigating some performance issues and stumbled upon this
> behavior:
>
> create function slowFunction() returns int as $$
> begin
> raise notice 'called';
> return 0;
> end;
> $$ language plpgsql volatile;
>
> create table something (i int, doSlow boolean);
> insert into something values(1, false),(2,false);
>
> select case when doSlow then sum(slowFunction()) else sum(1) end as total
> from something group by doSlow
>
> Postgres will correctly evaluate the last query, but it will also call the
> plpgsql volatile function.
>
> Luckly for us, this was read only function, but Postgres will happily
> execute DML commands inside it ;(

This is the expected behavior. You might look into FILTER clauses in
your queries, which might be closer to the behavior you want.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate