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.

Thanks
Craig

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

> 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Jun 21, 2005, at 5:59 PM, postgresql@bryden.co.za wrote:

> 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?

The pg_proc table (which contains the functions) includes a field
that has an argument list array. You can use this to figure out which
if the one you want to drop exists.

http://www.postgresql.org/docs/8.0/i...g-pg-proc.html

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

On Tue, Jun 21, 2005 at 09:16:08 +0200,
postgresql@bryden.co.za wrote:

> 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

> 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.

If the script isn't running in a single transaction, consider just
dropping the table and ignoring any error messages.

If you do need to worry about a failed drop aborting a transaction, then
you can use savepoints in 8.0. However, it doesn't look like you can
have conditional rollbacks in psql until 8.1. So to use this feature
in a script you will need to write a function that traps the exception
and rolls back to the the savepoint for the case where the drop fails.

For pre 8.0 versions, consider having a function that checks the system
catalog before issuing the drop.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Tue, Jun 21, 2005 at 10:59:58AM +0200, postgresql@bryden.co.za wrote:
> 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.

If you're specifically worried about functions, why not just use CREATE
OR REPLACE?
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org