DBschema restore

From: "Richard Sydney-Smith" <richard(at)ibisaustralia(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: DBschema restore
Date: 2003-09-20 04:09:15
Message-ID: 001801c37f2c$f7abdcf0$6d611ad3@athlon2000
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Muhyiddin A.M Hayat 2003-09-20 05:10:10 auto_increment
Previous Message Yasir Malik 2003-09-20 00:04:51 Re: virus warning