Обсуждение: 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.

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