Skip site navigation (1) Skip section navigation (2)

Displaying optimized CASE expressions (was Re: Nested CASE-WHEN scoping)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Displaying optimized CASE expressions (was Re: Nested CASE-WHEN scoping)
Date: 2011-05-25 22:56:33
Message-ID: 29136.1306364193@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> BTW, i just stumbled into this:

>> postgres=# explain verbose SELECT CASE now() WHEN (29+random()::int4) 
>> THEN 'foo' ELSE 'bar' END;
>> ERROR:  unexpected CASE WHEN clause: 326

>> Looks like ruleutils.c is also not prepared for the case that the 
>> implicit equality operation gets inlined into something else than an OpExpr.

> Grumble ... I thought we'd fixed that ...

Yeah, you're right.  We've hacked that code so it can handle some
transformations that the optimizer might apply, but inlining some random
expression to replace the equality operator is far beyond what we can
hope to deal with.

For those following along at home, the point is that if the user writes

	CASE test_expr WHEN cmp_expr THEN ...

the parser identifies the equality operator to use and produces
something that looks like this:

	CASE test_expr WHEN CaseTestExpr = cmp_expr THEN ...

We really need ruleutils.c to generate the original form when it is
looking at a stored rule (eg a view), so it goes to some lengths to
recognize "CaseTestExpr = something" in a WHEN clause and only print the
"something".  However, this example shows that there's no chance of
always being able to do that when looking at an expression that's been
through the planner.

I think what we'd better do, if we don't see something that looks like
"CaseTestExpr = something", is just print whatever we do have in the
WHEN clause.  That will require inventing a print representation for
CaseTestExpr, since in most cases that's going to appear in there
somewhere.  I suggest we just print CASE_TEST_EXPR, but if anyone wants
to bikeshed, feel free ...

Note that if Heikki does what I suggested upthread, the display will
eventually probably look like "$nn" instead (since it'll be a Param not
a CaseTestExpr).  But that's 9.2 or later material.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-05-25 23:11:31
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Previous:From: Tim UckunDate: 2011-05-25 22:32:56
Subject: Re: [BUGS] BUG #6034: pg_upgrade fails when it should not.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group