Re: SQL not showing in generated report

On Tue, Dec 13, 2011 at 2:22 AM, Guillaume Lelarge
<guillaume(at)lelarge(dot)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 | 17875
pg_toast_19341_index | 17875
rick_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 NULL THEN '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 NOT NULL
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
LEFT JOIN 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
LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid
LEFT JOIN pg_trigger tg ON dep.objid=tg.oid
LEFT JOIN pg_type ty ON dep.objid=ty.oid
LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid
LEFT JOIN pg_constraint co ON dep.objid=co.oid
LEFT JOIN pg_class coc ON co.conrelid=coc.oid
LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid
LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid
LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid
LEFT JOIN pg_language la ON dep.objid=la.oid
LEFT JOIN pg_namespace ns ON dep.objid=ns.oid
LEFT JOIN 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)