Fwd: schemas not appearing in pgAdmin

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

2007/10/24, Derek Fonda <Derek(dot)Fonda(at)level3(dot)com>:
>
> 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
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

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