pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtrouble with getting the field names
2+ messages / 1 participants
[nested] [flat]
* trouble with getting the field names
@ 2009-10-26 23:41 John <jfabiani@yolo.com>
0 siblings, 1 reply; 2+ messages in thread
From: John @ 2009-10-26 23:41 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: trouble with getting the field names
@ 2009-10-27 02:02 John <jfabiani@yolo.com>
parent: John <jfabiani@yolo.com>
0 siblings, 0 replies; 2+ messages in thread
From: John @ 2009-10-27 02:02 UTC (permalink / raw)
To: pgsql-sql
On Monday 26 October 2009 04:41:49 pm John wrote:
> 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
OK I discovered the problem I needed to add
… ALTER USER test SET search_path TO schema1,schema2
thanks
Johnf
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-10-27 02:02 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-26 23:41 trouble with getting the field names John <jfabiani@yolo.com>
2009-10-27 02:02 ` John <jfabiani@yolo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox