Unfortunately I managed to create a problematic database. The problem
occurs if I execute pg_dump and then try to reload the database. Details:
My only definitions in this database are:
> create function x(int4) returns bool as 'select $1 > 10;' language 'sql';
> create table y(z int4 check(x(z)));
Then, after pg_dump I got the next dump:
\connect - postgres
CREATE TABLE "y" ("z" int4,CONSTRAINT "y_z" CHECK (x(z))
);
CREATE FUNCTION "x" (int4 ) RETURNS bool AS 'select $1 > 10;' LANGUAGE 'SQL';
COPY "y" FROM stdin;
\.
Then, loading this dump back into the dropped and recreated database, I
got error messages, because the FUNCTION "x" is not created before the
TABLE "y".
The problem doesn't seems to be solved too easily. In fact, it is not a
good solution that FUNCTIONs are dumped before the TABLEs, because
the 'SQL' FUNCTIONs use the TABLEs in general (and the parser tries to
detect the SQL query). What to do? We need to store some kind of hierarchy
between table and function definitions, and after that could we say
something about the regenerating order. Of course, it needs at least one
new table among the system tables and needs pg_dump to be a bit more
complicated.
Regards,
Zoltan
------------------------------------------------------------------------------
KOVACS, Zoltan tip@pc10.radnoti-szeged.sulinet.hu