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/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
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