Thread: Checking if Aggregate exists
Hi,
I have an upgrade script that is supposed to install items into a
postgresql database if they don't already exist. One of the items I'm
having a hard time with is aggregates. I want to check if aggregate
foo doesn't exist, then run an SQL command to generate it.
if (check_aggregate_exists('foo') === false) {
$db->execute("CREATE AGGREGATE foo ....");
}
My code to check if an aggregate exists runs this query:
SELECT * FROM pg_catalog.pg_aggretate WHERE aggfnoid = 'foo'::REGPROC;
That works great IF foo exists, problem is if foo doesn't exist when
this query runs I get an error:
ERROR: function "foo" does not exist
The error causes my transaction to abort and rollback all the changes
I had already made in my update script.
Is there a better way to do this? Maybe a stored proc that eats the
error message?
Thanks,
Josh
On Mon, Mar 24, 2008 at 12:47:43PM -0500, Josh Trutwin wrote: > My code to check if an aggregate exists runs this query: > > SELECT * FROM pg_catalog.pg_aggretate WHERE aggfnoid = 'foo'::REGPROC; Seems to me you'd rather want the proisagg column in pg_proc and forget about pg_aggregate altogether... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.
Attachment
Josh Trutwin wrote: > Is there a better way to do this? Maybe a stored proc that eats the > error message? Well, you can use a plpgsql function with a BEGIN/EXCEPTION/END block to "eat" the error message. However I'm wondering whether you should be checking the aggregate argument type(s) as well. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Mar 24, 2008, at 1:09 PM, Martijn van Oosterhout wrote: > On Mon, Mar 24, 2008 at 12:47:43PM -0500, Josh Trutwin wrote: >> My code to check if an aggregate exists runs this query: >> >> SELECT * FROM pg_catalog.pg_aggretate WHERE aggfnoid = >> 'foo'::REGPROC; > > Seems to me you'd rather want the proisagg column in pg_proc and > forget > about pg_aggregate altogether... Also, the idiom for checking if something is present is normally: SELECT 1 FROM some_table WHERE ...; This way you aren't dealing with errors, if it doesn't exist the query simply doesn't return any results. 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
On Mon, 24 Mar 2008 14:02:02 -0500 Erik Jones <erik@myemma.com> wrote: > > On Mar 24, 2008, at 1:09 PM, Martijn van Oosterhout wrote: > > On Mon, Mar 24, 2008 at 12:47:43PM -0500, Josh Trutwin wrote: > >> My code to check if an aggregate exists runs this query: > >> > >> SELECT * FROM pg_catalog.pg_aggretate WHERE aggfnoid = > >> 'foo'::REGPROC; > > > > Seems to me you'd rather want the proisagg column in pg_proc and > > forget > > about pg_aggregate altogether... > > Also, the idiom for checking if something is present is normally: > > SELECT 1 FROM some_table WHERE ...; > > This way you aren't dealing with errors, if it doesn't exist the > query simply doesn't return any results. This one still does return an error though I think because of the cast: select 1 from pg_catalog.pg_aggregate where aggfnoid = 'foo'::regproc; ERROR: function "foo" does not exist Thanks, Josh
On Mar 24, 2008, at 2:18 PM, Josh Trutwin wrote: > On Mon, 24 Mar 2008 14:02:02 -0500 > Erik Jones <erik@myemma.com> wrote: > >> >> On Mar 24, 2008, at 1:09 PM, Martijn van Oosterhout wrote: >>> On Mon, Mar 24, 2008 at 12:47:43PM -0500, Josh Trutwin wrote: >>>> My code to check if an aggregate exists runs this query: >>>> >>>> SELECT * FROM pg_catalog.pg_aggretate WHERE aggfnoid = >>>> 'foo'::REGPROC; >>> >>> Seems to me you'd rather want the proisagg column in pg_proc and >>> forget >>> about pg_aggregate altogether... >> >> Also, the idiom for checking if something is present is normally: >> >> SELECT 1 FROM some_table WHERE ...; >> >> This way you aren't dealing with errors, if it doesn't exist the >> query simply doesn't return any results. > > This one still does return an error though I think because of the > cast: > > select 1 from pg_catalog.pg_aggregate where aggfnoid = > 'foo'::regproc; > > ERROR: function "foo" does not exist As Martijn pointed out, use pg_proc instead of pg_aggregate: SELECT 1 from pg_proc WHERE proname='foo' AND proisagg IS TRUE; And, as Alvarro pointed out in another reply, you'll probably want to include conditions in your where clause for the argument types. 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 wrote: > > On Mar 24, 2008, at 2:18 PM, Josh Trutwin wrote: >> On Mon, 24 Mar 2008 14:02:02 -0500 >> Erik Jones <erik@myemma.com> wrote: >> >>> >>> On Mar 24, 2008, at 1:09 PM, Martijn van Oosterhout wrote: >>>> On Mon, Mar 24, 2008 at 12:47:43PM -0500, Josh Trutwin wrote: >>>>> My code to check if an aggregate exists runs this query: >>>>> >>>>> SELECT * FROM pg_catalog.pg_aggretate WHERE aggfnoid = >>>>> 'foo'::REGPROC; >>>> >>>> Seems to me you'd rather want the proisagg column in pg_proc and >>>> forget >>>> about pg_aggregate altogether... >>> >>> Also, the idiom for checking if something is present is normally: >>> >>> SELECT 1 FROM some_table WHERE ...; >>> >>> This way you aren't dealing with errors, if it doesn't exist the >>> query simply doesn't return any results. >> >> This one still does return an error though I think because of the >> cast: >> >> select 1 from pg_catalog.pg_aggregate where aggfnoid = >> 'foo'::regproc; >> >> ERROR: function "foo" does not exist > > As Martijn pointed out, use pg_proc instead of pg_aggregate: > > SELECT 1 from pg_proc WHERE proname='foo' AND proisagg IS TRUE; > > And, as Alvarro pointed out in another reply, you'll probably want to > include conditions in your where clause for the argument types. > Now I'd go the other way and SELECT count(*) FROM.... If it ain't there you get 0 returned - no errors. In your function you can test >0 and return true else false or you can return the count and test numerically against the return. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz