Create view with substitution for values in one column

#11

On 29.08.2010 12:25, Thomas Kellerer wrote:

Quote:

Robert Klemme wrote on 29.08.2010 11:41:
I would have suggested CASE as well. But is the NULL check really
needed? IMHO "bonus > 0" can only be true if bonus is not 0. This is

Just to avoid confusion: the sentence above should have read "... if
bonus is not NULL".

Quote:

basic SQL standard, isn't it?

True

OK, thanks for confirming!

Quote:

Btw, I am not yet familiar with PostgreSQL's security model but the
whole exercise might be moot if you cannot prevent access to the base
table for those who are only allowed to see the flag version of the
incentive.

Just revoke the SELECT privilege on the table and grant the (only) on
the view.

Well, that's easy then. Looks like PostgreSQL is very similar to big O
in various areas. That's good to know.. ;-)

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

#12

On 2010-08-28, Julia Jacobson <julia.jacobson (AT) arcor (DOT) de> wrote:

Quote:

So, the select would look something like this:

select ename, empno,job,mgr,sal,cast(bonus as int)::boolean
from emp
where deptno=10
order by sal desc;

Thanks a lot for your answer.
Unfortunately, there is one exception where your solution doesn't work:
If the bonus is less than 1 dollar, for example 0.50 dollar,
your query would indicate that the employee didn't get a bonus.
In this case,
select case
when bonus is not null and bonus > 0 then true
else false
end
from employees
would be better.

or just

select bonus is not null and bonus > 0

the expression is already boolean

--
¡spuɐɥ ou 'ɐꟽ ʞooꞀ

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

#13

Quote:

select bonus is not null and bonus> 0

Thanks a lot. I like this really elegant solution.

#14

On Sun, 29 Aug 2010 11:10:34 +0000, Jasen Betts wrote:

Quote:

or just

select bonus is not null and bonus > 0

the expression is already boolean

Now, this is the best solution so far but the "not null" condition is
redundant. select bonus>0 would suffice.

--
http://mgogala.byethost5.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Create view with substitution for values in one column

comp.databases.postgresql comp.databases.postgresql

Re: Create view with substitution for values in one column - 08-29-2010 , 05:35 AM

Re: Create view with substitution for values in one column - 08-29-2010 , 06:10 AM

Re: Create view with substitution for values in one column - 08-29-2010 , 08:06 AM

Re: Create view with substitution for values in one column - 08-29-2010 , 11:51 AM