Re: Confused by result of pg_catalog.format_type()

On Apr 24, 2008, at 5:10 PM, Tom Lane wrote:

> Erik Jones <erik@myemma.com> writes:
>> Ah, pg_catalog.format_type(prorettype, null) = '"trigger"', thanks.
>
> It's probably fair to ask what it is you want to accomplish here,
> because comparing format_type's output to a constant seems awfully
> fragile.  Aside from the quotes (which I believe 8.3 won't emit in
> this particular case) the output can vary depending on search_path
> and perhaps other factors.
>
> If you're trying to identify trigger functions I'd suggest
>
>     where prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
>
> as being the most bulletproof formulation, and probably faster too.

Ah, thank you for the advice!  I'm writing python unittest assertions
for testing db state -- extremely useful for testing some custom
client db tools I'm writing for partitioning and migrations (both data
and schema).  I'd known about, and used, the regclass oid type, but
had only learned about it from seeing someone else's example.  I
should've read the chapter on system information function a little
more thoroughly :)  My assertion methods are about to become a lot
shorter...

Erik Jones

DBA | 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