Thread: problems viewing information_schema.schemata

problems viewing information_schema.schemata

From

"Little, Douglas"

Date:

27 July 2011, 16:59:23

Hi,

Some of my users are using sqldbx and it’s not working due to a dependence on displaying the content of information_schema.schemata

When I query the view I see content. When they do it, nothing – 0 rows – from sqldbx, pgadmin or other client.

I suspect that they don’t have access to a catalog table, but can’t find it.

What is the definition for schemata?

Thanks

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide

500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

Attachment

image001.jpg

Re: problems viewing information_schema.schemata

From

sqldbxdeveloper

Date:

28 July 2011, 02:21:20

I did investigate this issue and the problem is pg_has_role function.

Take a look at information_schema.schemata definition below
SELECT (current_database ()) ::information_schema.sql_identifier AS
catalog_name
    , (n.nspname) ::information_schema.sql_identifier AS schema_name
    , (u.rolname) ::information_schema.sql_identifier AS schema_owner
    , (NULL::character VARYING) ::information_schema.sql_identifier AS
default_character_set_catalog
    , (NULL::character VARYING) ::information_schema.sql_identifier AS
default_character_set_schema
    , (NULL::character VARYING) ::information_schema.sql_identifier AS
default_character_set_name
    , (NULL::character VARYING) ::information_schema.character_data AS
sql_path
FROM pg_namespace n
    , pg_authid u
WHERE ((n.nspowner = u.oid) AND pg_has_role (n.nspowner, 'USAGE'::text));


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/problems-viewing-information-schema-schemata-tp4639500p4640939.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.