Holger Klawitter <holger@klawitter.de> writes:
| > are there plans for an ALTER FUNCTION statement for Postgresql? I
| > think functions are completely unuseable, when it is not possible to
| > change the definition of a function. A bugfix in a function requires
| > the export of all data, a redefinition of the function and a complete
| > reimport. Or is there a simpler way?
|
| It might sound simple minded, but
|
| BEGIN WORK;
| LOCK TABLE t1;
| ...
| DROP FUNCTION ...
| CREATE FUNCTION ...
| ...
| UNLOCK TABLE t1;
| COMMIT WORK;
|
| should work. Whether you actually have to lock the tables depends on your
| application. (unplugging your host from the net might be easier :-)
I think I didn't have expained the problem well enough:
Take a look at this example:
First I create a function that checks if the argument is 1:
users=> CREATE FUNCTION check_func(int) RETURNS boolean AS '
users'> BEGIN
users'> IF $1 = 1 THEN
users'> RETURN TRUE;
users'> ELSE
users'> RETURN FALSE;
users'> END IF;
users'> END;
users'> ' LANGUAGE 'plpgsql';
CREATE
Then I create a table that uses the function as an CHECK constrain:
users=> CREATE TABLE data_table
users-> (
users-> data int CHECK (check_func(data))
users-> );
CREATE
Now I can insert data into the table:
users=> INSERT INTO data_table (data) VALUES (1);
INSERT 341478 1
Later I find out that my check constrain was wrong and I drop the
function and create the new one:
users=> DROP FUNCTION check_func(int);
DROP
users=> CREATE FUNCTION check_func(int) RETURNS boolean AS '
users'> BEGIN
users'> IF $1 = 2 THEN
users'> RETURN TRUE;
users'> ELSE
users'> RETURN FALSE;
users'> END IF;
users'> END;
users'> ' LANGUAGE 'plpgsql';
CREATE
Now I insert the new data and find out that the data_table is broken:
users=> INSERT INTO data_table VALUES (1);
ERROR: init_fcache: Cache lookup failed for procedure 341467
Locking does not help here.
bis später...
Sascha
