Re: I remember why I suggested CREATE FUNCTION...AS NULL

On Fri, 8 Sep 2000, Mike Mascari wrote:
> The solution tossed around is to create a SQL command such as
> "ALTER FUNCTION" or "CREATE OR REPLACE FUNCTION", etc. Under the
> assumption that such a command were to exist, its sole purpose
> would be to change the function implementation without changing
> the OID. 
> 
> Now back to pg_dump. Since the temporary solution to eliminating
> dependency problems is to dump in OID order, with the current
> code, things won't break. But with an ALTER FUNCTION, dumping in
> OID order could very well break the schema:
> 
> CREATE TABLE employees (key int4);
> 
> CREATE FUNCTION numpeople() RETURNS int4 AS
>  'SELECT COUNT(*) FROM employees' LANGUAGE 'plpgsql';
> 
> CREATE VIEW AS SELECT numpeople();
> 
> CREATE TABLE managers (key int4);
> 
> ALTER FUNCTION numpeople() AS 
>  'SELECT COUNT(*) FROM managers' LANGUAGE 'plpgsql';

Actually, I think this would still work, because it
doesn't check the table name existance until it's 
used for the first time, not when it's created.

> So what to do?
> 
> 1) Don't create an ALTER FUNCTION command?
> 2) Change pg_dump to walk through dependencies?
> 3) Or devise a manner by which pg_dump can dump objects in a
> certain sequence such that dependencies never break?

Well, I was discussing something related a few weeks (?) ago
for constraints, some kind of system that kept track of
what objects were dependent on what other objects.  Unfortunately,
it's not possible to do it completely without an awful lot
of work because you could have arguments to functions that
turned into objects to be dependent to.

You could limit the effect by defining the functions as NULL
(if you were to do that) in oid order and changing their code
later in the dump.  That way all of the objects are there
but may not be usable at that time.