CASE eval issue

From: Aaron Burt <aaron(at)bavariati(dot)org>
To: pdxpug(at)postgresql(dot)org
Subject: CASE eval issue
Date: 2011-10-18 22:31:31
Message-ID: 20111018223131.GD21112@kailarose.respond2.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pdxpug

Howdy, lovely PDXPUGgers!

I haven't been able to find a discussion of this issue, so maybe it has an
obvious answer. :)

Do CASE statements pre-evaluate all possible results on PG 8.4?

I'm working with column permissions on 8.4, and trying to make a SELECT
that returns a column if the user is permitted to read that column, and
'NA' if not. Unfortunately, the permissions system doesn't like it.

Query:
select id,
case has_column_privilege('mytable', 'secure_column', 'SELECT')
when FALSE then 'NA'
else secure_column
end as secure_column
from mytable where id=12345;

I get an "ERROR: permission denied for relation mytable" - even though it
shouldn't be accessing the column if it doesn't have permissions.

Even if I use a subselect instead of just the column name, it fails.
But if I use a column that's permitted, no problem.

Weirrrd...

Responses

Browse pdxpug by date

  From Date Subject
Next Message David E. Wheeler 2011-10-18 22:42:10 Re: CASE eval issue
Previous Message Rich Shepard 2011-10-14 15:43:04 Specifying Host With psycopg2