Thread: Scripting issues
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
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?"