From: John <jfabiani@yolo.com>
To: pgsql-sql@postgresql.org
Subject: trouble with getting the field names
Date: Mon, 26 Oct 2009 16:41:49 -0700
User-Agent: KMail/1.9.9
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200910261641.49211.jfabiani@yolo.com>

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