Re: Weird "CURRENT_SCHEMA" behavior

From: Mladen Gogala <no_at_email.here.invalid>
Date: Wed, 25 Nov 2009 19:30:59 +0000 (UTC)
Message-ID: <pan.2009.11.25.19.30.59_at_email.here.invalid>

On Wed, 25 Nov 2009 09:41:37 -0500, Serge Rielau wrote:

> Mladen,
>
> Could it be you are working with more than one DBMS vendor. What you
> expect is the behavior in e.g. DB2 where USER <> SCHEMA.
>
> Cheers
> Serge

I am working with Oracle and PostgreSQL where SCHEMA != USER. Here is an interesting query in PostgreSQL:
mgogala_at_nycwxp2622:~$ psql -U scott -h localhost Password for user scott:
psql (8.4.1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help.

scott=> with emp1 as (select ename,
scott(>                      deptno,
scott(>                      rank() over 
scott(>                        (partition by deptno order by sal) as 
salrank
scott(>               from emp)

scott-> select ename,deptno,salrank from emp1 where salrank=1;  ename | deptno | salrank
--------+--------+---------

 MILLER |     10 |       1
 SMITH  |     20 |       1
 JAMES  |     30 |       1

(3 rows)

You have 3 guesses to guess the password for user scott. There is also an interesting addition to Oracle capabilities with "recursive" subquery factoring.

-- 
http://mgogala.byethost5.com

Received on Wed Nov 25 2009 - 13:30:59 CST