Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER FUNCTION

ALTER FUNCTION

From
Sascha Ziemann
Date:
Hi,

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?

Sascha

Re: [GENERAL] ALTER FUNCTION

From
"Moray McConnachie"
Date:
UPDATE pg_proc SET prosrc='SQL statement' WHERE proname LIKE
'functionname';

seems to work for SQL queries at least. I doubt it's recommended,
though!

Moray

----------------------------------------------------------------------
----------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Sascha Ziemann <szi@khs-ag.de>
To: <pgsql-general@postgreSQL.org>
Sent: Thursday, December 02, 1999 11:39 AM
Subject: [GENERAL] ALTER FUNCTION


> Hi,
>
> 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?
>
> Sascha
>
> ************
>
>

Re: [GENERAL] ALTER FUNCTION

From
Adriaan Joubert
Date:
Just drop the function, drop all triggers that use the function,
re-create the function and recreate all triggers. If the function is
called by other PL functions, you need to drop and re-install those as
well. If you keep them all in a big file, every one preceded by drop,
you can just reload the file (with \i into psql) whenever you have
changed something. No need to dump any data.

Adriaan



>
> UPDATE pg_proc SET prosrc='SQL statement' WHERE proname LIKE
> 'functionname';
>
> seems to work for SQL queries at least. I doubt it's recommended,
> though!
>
> >
> > 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?

Re: [GENERAL] ALTER FUNCTION

From
Sascha Ziemann
Date:
Adriaan Joubert <a.joubert@albourne.com> writes:

| Just drop the function, drop all triggers that use the function,
| re-create the function and recreate all triggers. If the function is
| called by other PL functions, you need to drop and re-install those as
| well. If you keep them all in a big file, every one preceded by drop,
| you can just reload the file (with \i into psql) whenever you have
| changed something. No need to dump any data.

When I use the function in a CHECK constrain of a table, I have to
destroy the table.  Or is it possible to refresh the reference to the
function by an ALTER TABLE statement?

By the way: when I drop a table that is used by another table via
INHERITS, I get the warning, that the table is used and that I can not
drop it.  Why are functions handled different?  I is really a problem,
when there are broken tables in a database and nobody knows it.

Sascha

Re: [GENERAL] ALTER FUNCTION

From
Sascha Ziemann
Date:
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