From: Richard Sydney-Smith <richard@ibisaustralia.com>
To: pgsql-sql@postgresql.org
Subject: DBschema restore
Date: Sat, 20 Sep 2003 12:09:15 +0800
Message-ID: <001801c37f2c$f7abdcf0$6d611ad3@athlon2000> (raw)
An example problem...
The dbschema file contains the following section ( which causes several errors)
....
-- Function: public.today()
CREATE FUNCTION public.today() RETURNS date AS 'select current_date;' LANGUAGE 'sql' VOLATILE;
-- View: public.today
CREATE VIEW public.today AS SELECT today() AS today;
-- Rule: _RETURN
CREATE RULE "_RETURN" AS ON SELECT TO today DO INSTEAD SELECT today() AS today;
-- Function: public.plpgsql_call_handler()
CREATE FUNCTION public.plpgsql_call_handler() RETURNS language_handler AS 'plpgsql_call_handler' LANGUAGE 'c' VOLATILE;
-- Language: plpgsql
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler;
.......
Errors are
(1) CREATE VIEW public.today AS SELECT today() AS today; complains a view with the same name already exists. However if I close/open pgadmin it works if executed on its own.
(2) CREATE RULE "_RETURN" AS ON SELECT TO today DO INSTEAD SELECT today() AS today; will not work at all. Says a view with the same name already exists
(3)
-- Function: public.plpgsql_call_handler()
CREATE FUNCTION public.plpgsql_call_handler() RETURNS language_handler AS 'plpgsql_call_handler' LANGUAGE 'c' VOLATILE;
-- Language: plpgsql
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler;
did not work but if i use ...
CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
'$libdir/plpgsql' LANGUAGE C;
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler;
it is ok
and so on it goes I have 74 functions , 128 tables and 34 views. With these and the other simular errors on the dbschema sql file I urgently need some guidance on how to fix these problems.
thanks
Richard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard@ibisaustralia.com
Subject: Re: DBschema restore
In-Reply-To: <001801c37f2c$f7abdcf0$6d611ad3@athlon2000>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox