Forum OpenACS Q&A: define PL/SQL and DML in separate files
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.