Thread: SQL not showing in generated report

SQL not showing in generated report

From

Rick Dicaire

Date:

12 December 2011, 18:02:04

Hi folks, I'm generating a dependents report on a db user, I highlight
the user as shown in Login Roles, right click -> reports -> Dependents
Report,
but I see the "Include the SQL in the report" checkbox greyed out (a
checkmark shows in it) in the dialog box.

Resultant report doesn't have the sql statement in it. I've tried both
xhtml and xml output.

I'm trying to see what sql statement is used to generate this report.

pgadmin3 1.14.1 Windows 7
postgresql 9.1.2

Thanks

Re: SQL not showing in generated report

From

Guillaume Lelarge

Date:

12 December 2011, 19:22:14

On Mon, 2011-12-12 at 14:01 -0500, Rick Dicaire wrote:
> Hi folks, I'm generating a dependents report on a db user, I highlight
> the user as shown in Login Roles, right click -> reports -> Dependents
> Report,
> but I see the "Include the SQL in the report" checkbox greyed out (a
> checkmark shows in it) in the dialog box.
>
> Resultant report doesn't have the sql statement in it. I've tried both
> xhtml and xml output.
>
> I'm trying to see what sql statement is used to generate this report.
>

It's something like the SQL in the file I attached. I hope I didn't
change by mistake while cleaning it. If it doesn't work, take a look at
the file pgadmin/schema/pgObject.cpp (two functions ShowDependents, and
ShowDependencies).


--
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Attachment

dependents.sql

Re: SQL not showing in generated report

From

Rick Dicaire

Date:

12 December 2011, 19:40:17

On Mon, Dec 12, 2011 at 3:21 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> On Mon, 2011-12-12 at 14:01 -0500, Rick Dicaire wrote:
>> Hi folks, I'm generating a dependents report on a db user, I highlight
>>
>> I'm trying to see what sql statement is used to generate this report.
>>
>
> It's something like the SQL in the file I attached. I hope I didn't
> change by mistake while cleaning it. If it doesn't work, take a look at
> the file pgadmin/schema/pgObject.cpp (two functions ShowDependents, and
> ShowDependencies).

Thanks, got a syntax error in your code...just to be clear, does the
dependents report on a login role show all objects owned by that login
role?

Re: SQL not showing in generated report

From

Guillaume Lelarge

Date:

13 December 2011, 06:23:03

On Mon, 2011-12-12 at 15:40 -0500, Rick Dicaire wrote:
> On Mon, Dec 12, 2011 at 3:21 PM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
> > On Mon, 2011-12-12 at 14:01 -0500, Rick Dicaire wrote:
> >> Hi folks, I'm generating a dependents report on a db user, I highlight
> >>
> >> I'm trying to see what sql statement is used to generate this report.
> >>
> >
> > It's something like the SQL in the file I attached. I hope I didn't
> > change by mistake while cleaning it. If it doesn't work, take a look at
> > the file pgadmin/schema/pgObject.cpp (two functions ShowDependents, and
> > ShowDependencies).
> 
> Thanks, got a syntax error in your code...

Did you add the role OID after "WHERE dep.refobjid="? if you didn't,
yeah, you'll get a syntax error.

> just to be clear, does the
> dependents report on a login role show all objects owned by that login
> role?
> 

Yes, but you'll get other stuff too.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com

Re: SQL not showing in generated report

From

Rick Dicaire

Date:

13 December 2011, 14:31:36

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)

Re: SQL not showing in generated report

From

Guillaume Lelarge

Date:

13 December 2011, 19:23:02

On Tue, 2011-12-13 at 10:31 -0500, Rick Dicaire wrote:
> 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       |    17875
>  pg_toast_19341_index |    17875
>  rick_test            |    17875
> (3 rows)
> 
> ################################################

It should have. My understanding is that, if you're owner of an object,
there is a dependency between the object and the user. Maybe my
understanding is wrong. Or maybe the SQL I gave you is wrong. You should
better look at the source file to make sure I didn't give you a wrong
SQL statement.


-- 
Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com