Postgres Pro
Downloads
Home   >   mailing lists

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

Generating sql to capture fully qualified table names???

From
smiley2211
Date:
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;

*****************

Thanks...Michelle :confused:


--
View this message in context:
http://www.nabble.com/Generating-sql-to-capture-fully-qualified-table-names----tf4654460.html#a13298439
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

From
Erik Jones
Date:
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@myemma.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

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

From
Osvaldo Rosario Kussama
Date:
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

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

From
smiley2211
Date:
THANK YOU BOTH ON YOUR REPLIES...THIS IS WHAT I WAS LOOKING FOR...

--
View this message in context:
http://www.nabble.com/Generating-sql-to-capture-fully-qualified-table-names----tf4654460.html#a13299646
Sent from the PostgreSQL - general mailing list archive at Nabble.com.