Thread: where-used function

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello All:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hope everyone is enjoying a peaceful holiday season.</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am using this quite time between the holidays to get my dev and production environment back into
sync.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">It would be very helpful, if I had a where-used function.  This function, given an object name,
woulddetermine</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">if the object exists in the database ( i.e. is it a table or a view)  and then, most important, what
otherobjects are dependant on the object.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">A database I worked with years ago, distributed by a marketing company in the NorthWest USA, had
sucha function.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">As I remember that function was buggy. </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Does anybody know if a where-used function exists for the wonderful database known as
Postgres?</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Many Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">KevinDuffy</span></font><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt"> </span></font></div>

"Kevin Duffy" <KD@wrinvestments.com> writes:
> It would be very helpful, if I had a where-used function.  This
> function, given an object name, would determine
> if the object exists in the database ( i.e. is it a table or a view)
> and then, most important, what other objects are dependant on the
> object.

The traditional manual way to do the latter is
begin;drop <object>;... read the error message about what the drop would cascade to ...rollback;

If you feel like getting down-and-dirty with the implementation you
could troll the contents of pg_depend for yourself.  I think it would
be kind of painful to do without writing some C code though, because
a lot of useful operations like getObjectDescription() aren't exposed
at the SQL level.
        regards, tom lane