Re: CASE eval issue

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