Re: [GENERAL] ALTER FUNCTION

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