Thread: DBschema restore
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;
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