Re: Generating sql to capture fully qualified table names??? - Mailing list pgsql-general

smiley2211 escreveu:
> 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 SELECT, INSERT, UPDATE, DELETE ON '  ||
        pn.nspname || '.' || pc.relname ||' TO newuser;'
           FROM pg_class pc JOIN pg_namespace pn
                ON (pc.relnamespace = pn.oid)
WHERE pc.relname !~ 'pg_*' AND relkind in ('r','v','S')
   AND pn.nspname != 'information_schema'
ORDER BY relname;

Osvaldo