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.g.johnston@gmail.com> wrote:
>> On Tue, Jul 3, 2018 at 11:41 AM, Igor Korot <ikorot01@gmail.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.klaver@aklaver.com