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