Hello,
I try Database explorer with Netbeans and Postgresql (it is same with forte and postgresql 7.3.2 jdbc)
I can't get tables, views , procedures.
Problem is schema name - btw. when Database explorer call DatabaseMetadata.getTables use only upper case
(no matter if schema lower case) and don't find anything.
I think that problem is Netbeans, because lower case schema name is allowed.
- schema (and user,tables, procedures etc) don't be set in upper case
BTW. I have user (schema) snpe :
Netbeans Database Explorer call next query for tables (query is ugly, but PostgreSQL haven't friendly catalog) :
SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,
CASE n.nspname LIKE 'pg\\_%'
WHEN true THEN
CASE n.nspname
WHEN 'pg_catalog' THEN
CASE c.relkind
WHEN 'r' THEN 'SYSTEM TABLE'
WHEN 'v' THEN 'SYSTEM VIEW'
WHEN 'i' THEN 'SYSTEM INDEX'
ELSE NULL END
WHEN 'pg_toast' THEN CASE c.relkind
WHEN 'r' THEN 'SYSTEM TOAST TABLE'
WHEN 'i' THEN 'SYSTEM TOAST INDEX'
ELSE NULL
END
ELSE CASE c.relkind
WHEN 'r' THEN 'TEMPORARY TABLE'
WHEN 'i' THEN 'TEMPORARY INDEX'
ELSE NULL
END
END
WHEN false THEN
CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN
'VIEW' ELSE NULL END ELSE NULL END
AS TABLE_TYPE,
d.description AS REMARKS
FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)
LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')
LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')
WHERE c.relnamespace = n.oid
** AND n.nspname LIKE 'SNPE'
AND c.relname LIKE '%'
AND (false OR ( c.relkind = 'r' AND n.nspname NOT LIKE 'pg\\_%' ) )
ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
Row with ** make problem
I try < AND n.nspname like 'snpe' > and it work.
Who will resolve : Netbeans or Postgresql JDBC people ?
regards
Haris Peco