Below is a SQL statement that was created to retreive the primary key , and column names and data types for a table name and a schema. The problem is that is works very well if I only use the 'public' schema. But it does not work if I use a 'system' schema I created (owned my me). The offending line is "AND pg_table_is_visible(c.oid)" which makes me believe I done something wrong with the roles? IOW if I drop the "pg_table_is_visible" it works with my 'system' schema. So I need a better guru than myself to help/tell me what I did wrong. Thanks in advance!
SELECT a.attname, t.typname, EXISTS(SELECT * FROM generate_series(0, 31) idx(n) WHERE a.attnum = i.indkey[idx.n]) AS isprimary FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid LEFT JOIN pg_index i ON i.indrelid = c.oid AND i.indisprimary WHERE c.relname = 'sys_company' AND n.nspname = 'system' AND a.attname NOT IN ('ctid', 'cmin', 'cmax', 'tableoid', 'xmax', 'xmin') AND has_schema_privilege(n.oid, 'usage') AND has_table_privilege(c.oid, 'select') AND pg_table_is_visible(c.oid) ORDER BY c.relname, a.attname Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql