This is a good example of why all PL/SQL and plpgsql code should be
defined, with 'create or replace', in their own SQL files, separate
from the 'create table', etc. DML statements. Then all you have to do
is re-source all those stock SQL files into the database, and all your
stored functions and procedures are nicely redefined.
In my own work with Oracle, way back when I adopted a naming
convention of "*-ph.sql" and "*-pb.sql" to indicate "package header"
and "package body" files, since most Oracle PL/SQL code should be in
PL/SQL packages. PostgreSQL doesn't have plpgsql packages, but at
least when I tweaked the OpenACS Static Pages package, I used the same
"*-pb.sql" naming convention for Postgres, to line up with Oracle.
(Note that it is also often useful to have the Oracle PL/SQL package
header and body in two separate files, but this is much less important
than having the PL/SQL definitions separate from the DML.)
The same goes for views and triggers, incidentally - they should be
separate from the DML so you can easily recreate them just by sourcing
the file. The general principle here is that stuff which you can
simply "create or replace" (stored procedure code, views, triggers)
should always be in a separate file from stuff which you can only
"create" (tables). This makes the logistics of maintenance and
upgrades substantially easier, and there's no drawback. Any more
detailed break out of stuff, like PL/SQL package bodies and headers in
separate, is just icing on the cake.
Maybe we could come up with some OpenACS recomended standard for this
sort of thing and move all the packages towards using it.