Thread: 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