trouble with getting the field names

From: John <jfabiani(at)yolo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: trouble with getting the field names
Date: 2009-10-26 23:41:49
Message-ID: 200910261641.49211.jfabiani@yolo.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message John 2009-10-27 02:02:55 Re: trouble with getting the field names
Previous Message Tom Lane 2009-10-24 15:46:48 Re: Table Valued Parameters