> postgresql@bryden.co.za wrote:
>> Hi
>>
>> I come from a MSSQL background and am trying to figure out how to write
>> deployment scripts for PostgreSQL. Typically, if I want to drop a
>> function, I would write a script that first checks for it's existence
>> and
>> then performs the drop.
>>
>> In MSSQL it would be as easy as (this can be done in native TSQL):
>> IF EXISTS (...some query to system tables...)
>> DROP function XXX
>>
>> However, I am really strugling to understand how to do this in
>> PostgreSQL.
>> It seem the standard SQL language doesn't support the IF statement.
>>
>> All the development that I do needs to be deployed in a script fashion
>> and
>> generally I need to check for the existence of an object before
>> replacing
>> or dropping.
>>
>> Any help will be much appreciated.
>
> It's simple enough to write a plpgsql function that takes two text
> parameters - execute the first and see if any rows are returned, then
> execute the second if any rows were.
>
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
Hi Richard
Thanks for the suggestion. I'm sure I'll go that way.
One other question: Since in PostgreSQL you can have "overloaded"
functions, how do you query the system tables for the existence of a
particular version of the function? I can query
information_schema.routines for the function name, but not for the
particular parameters.
Thanks
Craig