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

Re: CASE eval issue

From: "David E(dot) Wheeler" <david(at)kineticode(dot)com>
To: Aaron Burt <aaron(at)bavariati(dot)org>
Cc: pdxpug(at)postgresql(dot)org
Subject: Re: CASE eval issue
Date: 2011-10-19 02:24:08
Message-ID: 6655753D-FB5B-4C88-9584-9EDCB29623A5@kineticode.com (view raw or flat)
Thread:
Lists: pdxpug
On Oct 18, 2011, at 4:30 PM, Aaron Burt wrote:

>> select id,
>>  case when has_column_privilege('mytable', 'secure_column', 'SELECT')
>>    then 'NA'
>>    else secure_column
>>  end as secure_column
>> from mytable where id=12345;
> 
> Not sure I follow you.  Your version appears to reverse the condition.
> I did also use the 'case when (condition) then ...' form but as one would
> expect, it acted the same.  I also used explicit cases for TRUE and FALSE.

Yeah, I guess it does evaluate the column. (And yes, I meant to reverse the `then` and `else` clauses, sorry.)

So you might need to use a function. Something like this (untested):

    CREATE OR REPLACE FUNCTION icanhaz(
        anid INT
    ) RETURNS TABLE (
        id            INT,
        secure_column TEXT
    ) LANGUAGE plpgsql AS $$
    BEGIN
        IF has_column_privilege('mytable', 'secure_column', 'SELECT') THEN
            RETURN QUERY SELECT id, 'NA' FROM mutable WHERE id = anid;
        END IF;
        RETURN QUERY SELECT id, secure_column FROM mutable WHERE id = anid;
    END;
    $$;

    SELECT * FROM icanhaz(12345);

HTH,

David

In response to

Responses

pdxpug by date

Next:From: gabrielleDate: 2011-10-19 17:15:45
Subject: Reminder: October meeting tomorrow!
Previous:From: Aaron BurtDate: 2011-10-18 23:30:52
Subject: Re: CASE eval issue

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