Scripting issues

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

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

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/interactive/catalog-pg-proc.html

Michael Glaesemann
grzm myrealbox com

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.

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