From: John <jfabiani@yolo.com>
To: pgsql-sql@postgresql.org
Subject: Re: trouble with getting the field names
Date: Mon, 26 Oct 2009 19:02:55 -0700
User-Agent: KMail/1.9.9
References: <200910261641.49211.jfabiani@yolo.com>
In-Reply-To: <200910261641.49211.jfabiani@yolo.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200910261902.55913.jfabiani@yolo.com>

On Monday 26 October 2009 04:41:49 pm John wrote:
> Below is a SQL statement that was created to retreive the primary key , a=
nd
> 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 n=
ot
> 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 =3D i.indkey[idx.n]) AS isprimary
> FROM pg_class c
> JOIN pg_namespace n ON n.oid =3D c.relnamespace
> JOIN pg_attribute a ON a.attrelid =3D c.oid
> JOIN pg_type t ON t.oid  =3D a.atttypid LEFT
> JOIN pg_index i ON i.indrelid =3D c.oid AND i.indisprimary
> WHERE c.relname =3D 'sys_company' AND n.nspname =3D '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=20
=E2=80=A6 ALTER USER test SET search_path TO schema1,schema2

thanks

Johnf