Re: Checking if Aggregate exists

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