Re: [GENERAL] ALTER FUNCTION

Holger Klawitter <holger(at)klawitter(dot)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