Re: Generating sql to capture fully qualified table names???

On Oct 19, 2007, at 11:44 AM, smiley2211 wrote:

>
> Hello all,
>
> I am using the query below to generate SQL code to grant access to
> objects -
> how do I get this statement to PULL the fully qualified name
> (schema.tablename)???
>
> *****************
> SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || relname ||
> ' TO newuser;'
> FROM pg_class
> where relname !~ 'pg_*' AND relkind in ('r','v','S') ORDER BY
> relname;
>
> *****************

SELECT 'GRANT ALL ON ' || n.nspname || '.' || c.relname ' TO newuser;'
FROM pg_class c, pg_namespace c
WHERE c.relnamespace=n.oid
and n.nspname = 'your_schema_name';

Erik Jones

Software Developer | Emma®
erik(at)myemma(dot)com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com