Обсуждение: schemas not appearing in pgAdmin

I'm running postgres 8.0.3 (Solaris) and PgAdmin III 1.8.0

I created a schema in my prod DB that is named pgmacdon, however it 
appears that pgAdmin III is not displaying this schema as existing in the 
prod DB.

It would appear that you are using the wrong query to exclude the pg_* 
internal schemas

PgAdmin III appears to be doing a query similar to the following to 
retrieve the list of schemas to display:

SELECT nspname, CASE WHEN nspname LIKE 'pg\_tmp\_%%' THEN 1      WHEN (nspname LIKE 'pg\_%') THEN 0      ELSE 3 END AS
nsptyp
FROM pg_namespace

      nspname       | nsptyp
--------------------+-------- pg_toast           |      0 pheller            |      3 pg_temp_1          |      0
pg_catalog        |      0 public             |      3 mdpeters           |      3 information_schema |      3 dfonda
         |      3 msessums           |      3 adbrown            |      3 philmacdonald      |      3 pgmacdon
|      0  <===== FALSELY being set to 0 (don't display)
 


It would appear that in order to match pg_*, postgres requires:

LIKE 'pg\\_%'
_rather than_
LIKE 'pg\_%'

Not sure if this was a bug in 8.0.3 that was later fixed or the syntax for 
a literal underbar MUST BE '\\_'


Thank you for your time and attention  Derek Fonda

---------- Forwarded message ----------
From: Alejandro Gasca <galejadror@gmail.com>
Date: 25-oct-2007 11:13
Subject: Re: [pgadmin-support] schemas not appearing in pgAdmin
To: Derek Fonda <Derek.Fonda@level3.com>





Same problem here, with postgres 8.2.5, pgAdmin 1.8.0  if i do:

create schema pgpg;

the schema doesn't appear.

by the way, why don't put:
    CASE
        WHEN nspname ~ '^pg_temp_' THEN 1
        WHEN (nspname ~ '^pg_') THEN 0
        ELSE 3
    END AS nsptyp,

instead of
    CASE
        WHEN nspname LIKE E'pg_temp_%%' THEN 1
                WHEN (nspname LIKE E'pg\_%') THEN 0
                ELSE 3
        END AS nsptyp,

thanks.

Alejandro

Derek Fonda wrote:
> I'm running postgres 8.0.3 (Solaris) and PgAdmin III 1.8.0
> 
> I created a schema in my prod DB that is named pgmacdon, however it
> appears that pgAdmin III is not displaying this schema as existing in
> the prod DB.
> 
> It would appear that you are using the wrong query to exclude the pg_*
> internal schemas
> 
> PgAdmin III appears to be doing a query similar to the following to
> retrieve the list of schemas to display:
> 
> SELECT
>  nspname,
>  CASE WHEN nspname LIKE 'pg\_tmp\_%%' THEN 1
>       WHEN (nspname LIKE 'pg\_%') THEN 0
>       ELSE 3 END AS nsptyp
> FROM
>  pg_namespace

Thanks - it was escaped, but because it was in a C string in the source
code it should have been double-escaped. Fixed in SVN for 1.8.

Regards, Dave.