Re: How to watch for schema changes

On 07/05/2018 08:40 AM, Igor Korot wrote:
> Hi, David,
>
> On Tue, Jul 3, 2018 at 1:46 PM, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>> On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot <ikorot01(at)gmail(dot)com> wrote:
>>>
>>>
>>> I presume threre is a query which check for the function/trigger
>>> existence? Something like:
>>>
>>> IF NOT EXIST(SELECT * FROM ) CREATE OR REPLACE FUNCTION....;
>>
>>
>> CREATE OR REPLACE is how you re-create a function that (whose
>> name/signature) might already exist; CREATE already assumes one doesn't
>> exist.
>
> Why do I need to re-create a function with exactly the same name and body

If you use CREATE OR REPLACE FUNCTION it will do just that each time you
call it. Seems like overkill to me. See below for another way.

> Can't I just check if such function exists?

Something like this:

SELECT
count(*)
FROM
pg_proc AS proc
JOIN
pg_namespace AS ns
ON
proc.pronamespace = ns.oid
WHERE
ns.nspname='public' -- Function schema
AND
proname = 'tag_rcv_undo' -- Function name
;

>
> Thank you.
>
>>
>> David J.
>>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com