Re: Checking if Aggregate exists

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(at)myemma(dot)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