Confused by result of pg_catalog.format_type()
Here's an example:
pagila=# select pg_catalog.format_type(prorettype, NULL) from pg_proc
where proname='foo_ins_trig';
format_type
-------------
"trigger"
(1 row)
Time: 3.212 ms
pagila=# SELECT 1
FROM pg_proc p
WHERE p.proname='foo_ins_trig'
AND pg_catalog.format_type(p.prorettype, NULL) = 'trigger';
?column?
----------
(0 rows)
Time: 0.736 ms
pagila=#
What am I missing?
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
Erik Jones <erik@myemma.com> writes:
What am I missing?
The double quotes in the function result ...
regards, tom lane
On Apr 24, 2008, at 4:58 PM, Tom Lane wrote:
Erik Jones <erik@myemma.com> writes:
What am I missing?
The double quotes in the function result ...
Ah, pg_catalog.format_type(prorettype, null) = '"trigger"', thanks.
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
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.
regards, tom lane
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