Re: SQL not showing in generated report

On Tue, Dec 13, 2011 at 2:22 AM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> Did you add the role OID after "WHERE dep.refobjid="? if you didn't,
> yeah, you'll get a syntax error.

Thanks...tried a few role OIDs for known object ownerships, this code
returned 0 rows:

select usename,usesysid from pg_user where usesysid=17875;usename  | usesysid
----------+----------rdicaire |    17875
(1 row)

###############################################

select relname,relowner from pg_class where relowner=17875;      relname        | relowner
----------------------+----------pg_toast_19341       |    17875pg_toast_19341_index |    17875rick_test            |
17875
 
(3 rows)

################################################

SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc,      CASE WHEN cl.relkind IS NOT NULL THEN
cl.relkind||
 
COALESCE(dep.objsubid::text, '')           WHEN tg.oid IS NOT NULL THEN 'T'::text           WHEN ty.oid IS NOT NULL
THEN'y'::text           WHEN ns.oid IS NOT NULL THEN 'n'::text           WHEN pr.oid IS NOT NULL THEN 'p'::text
 WHEN la.oid IS NOT NULL THEN 'l'::text           WHEN rw.oid IS NOT NULL THEN 'R'::text           WHEN co.oid IS NOT
NULLTHEN 'C'::text || contype           WHEN ad.oid IS NOT NULL THEN 'A'::text           ELSE '' END AS type,
COALESCE(coc.relname,clrw.relname) AS ownertable,      CASE WHEN cl.relname IS NOT NULL            AND att.attname IS
NOTNULL           THEN cl.relname || '.' || att.attname           ELSE COALESCE(cl.relname, co.conname, pr.proname,
tg.tgname,                        ty.typname, la.lanname, rw.rulename, ns.nspname)      END AS refname,
COALESCE(nsc.nspname,nso.nspname, nsp.nspname,               nst.nspname, nsrw.nspname) AS nspname FROM pg_depend dep
LEFTJOIN pg_class cl ON dep.objid=cl.oid LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND
 
dep.objsubid=att.attnum LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid LEFT JOIN pg_proc pr ON dep.objid=pr.oid
LEFTJOIN pg_namespace nsp ON pr.pronamespace=nsp.oid LEFT JOIN pg_trigger tg ON dep.objid=tg.oid LEFT JOIN pg_type ty
ONdep.objid=ty.oid LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid LEFT JOIN pg_constraint co ON dep.objid=co.oid
LEFTJOIN pg_class coc ON co.conrelid=coc.oid LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid LEFT JOIN pg_rewrite
rwON dep.objid=rw.oid LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class LEFT JOIN pg_namespace nsrw ON
clrw.relnamespace=nsrw.oidLEFT JOIN pg_language la ON dep.objid=la.oid LEFT JOIN pg_namespace ns ON dep.objid=ns.oid
LEFTJOIN pg_attrdef ad ON ad.oid=dep.objid
 
WHERE dep.refobjid=17875
AND classid IN ( SELECT oid FROM pg_class WHERE relname IN ('pg_class', 'pg_constraint', 'pg_conversion',
'pg_language',                  'pg_proc', 'pg_rewrite', 'pg_namespace', 'pg_trigger',                   'pg_type',
'pg_attrdef'))
ORDER BY classid, cl.relkind;deptype | classid | relkind | adbin | adsrc | type | ownertable |
refname | nspname
---------+---------+---------+-------+-------+------+------------+---------+---------
(0 rows)