X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 3B3C1D1B4F0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 20 Sep 2003 04:07:35 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 44455-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 20 Sep 2003 01:07:03 -0300 (ADT)
Received: from server146.hostingrevolution.com
	(server146.hostingrevolution.com [64.21.143.245])
	by svr1.postgresql.org (Postfix) with ESMTP id C944DD1B51E
	for <pgsql-sql@postgresql.org>; Sat, 20 Sep 2003 01:07:02 -0300 (ADT)
Received: from 109.b.004.pth.iprimus.net.au ([211.26.97.109] helo=athlon2000)
	by server146.hostingrevolution.com with smtp (Exim 4.23)
	id 1A0Z16-0005By-T7
	for pgsql-sql@postgresql.org; Fri, 19 Sep 2003 23:06:19 -0500
Message-ID: <001801c37f2c$f7abdcf0$6d611ad3@athlon2000>
From: "Richard Sydney-Smith" <richard@ibisaustralia.com>
To: <pgsql-sql@postgresql.org>
Subject: DBschema restore
Date: Sat, 20 Sep 2003 12:09:15 +0800
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0015_01C37F70.02CA8F80"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - server146.hostingrevolution.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12]
X-AntiAbuse: Sender Address Domain - ibisaustralia.com
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=1.3 tagged_above=0.0 required=5.0 tests=BAYES_30,
	HTML_10_20, UPPERCASE_25_50
X-Spam-Level: *
X-Archive-Number: 200309/211
X-Sequence-Number: 15189

This is a multi-part message in MIME format.

------=_NextPart_000_0015_01C37F70.02CA8F80
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

An example problem...

The dbschema file contains the following section ( which causes several err=
ors)

....


-- Function: public.today()
CREATE FUNCTION public.today() RETURNS date AS 'select current_date;'  LANG=
UAGE '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 to=
day;

-- 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 w=
ith 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() A=
S today; will not work at all. Says a view with the same name already exists

(3)=20
-- 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 thes=
e and the other simular errors on the dbschema sql file I urgently need som=
e guidance on how to fix these problems.

thanks

Richard

------=_NextPart_000_0015_01C37F70.02CA8F80
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff background=3D"">
<DIV><FONT face=3DArial size=3D2>An example problem...<BR><BR>The dbschema =
file=20
contains the following section ( which causes several=20
errors)<BR><BR>....<BR><BR><BR>-- Function: public.today()<BR>CREATE FUNCTI=
ON=20
public.today() RETURNS date AS 'select current_date;'&nbsp; LANGUAGE 'sql'=
=20
VOLATILE;<BR><BR>-- View: public.today<BR>CREATE VIEW public.today AS SELEC=
T=20
today() AS today;<BR><BR>-- Rule: _RETURN<BR>CREATE RULE "_RETURN" AS ON SE=
LECT=20
TO today DO INSTEAD SELECT today() AS today;<BR><BR>-- Function:=20
public.plpgsql_call_handler()<BR>CREATE FUNCTION public.plpgsql_call_handle=
r()=20
RETURNS language_handler AS 'plpgsql_call_handler'&nbsp; LANGUAGE 'c'=20
VOLATILE;<BR><BR>-- Language: plpgsql<BR>CREATE TRUSTED PROCEDURAL LANGUAGE=
=20
'plpgsql'<BR>&nbsp; HANDLER plpgsql_call_handler;<BR><BR>.......<BR><BR>Err=
ors=20
are<BR>(1) CREATE VIEW public.today AS SELECT today() AS today; complains a=
 view=20
with the same name already exists. However if I close/open pgadmin it works=
 if=20
executed on its own.<BR><BR>(2) CREATE RULE "_RETURN" AS ON SELECT TO today=
 DO=20
INSTEAD SELECT today() AS today; will not work at all. Says a view with the=
 same=20
name already exists<BR><BR>(3) <BR>-- Function:=20
public.plpgsql_call_handler()<BR>CREATE FUNCTION public.plpgsql_call_handle=
r()=20
RETURNS language_handler AS 'plpgsql_call_handler'&nbsp; LANGUAGE 'c'=20
VOLATILE;<BR><BR>-- Language: plpgsql<BR>CREATE TRUSTED PROCEDURAL LANGUAGE=
=20
'plpgsql'<BR>&nbsp; HANDLER plpgsql_call_handler;<BR><BR>did not work but i=
f i=20
use ...<BR><BR>CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HAN=
DLER=20
AS<BR>'$libdir/plpgsql' LANGUAGE C;<BR>CREATE TRUSTED PROCEDURAL LANGUAGE=
=20
plpgsql<BR>HANDLER plpgsql_call_handler;<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>it is ok</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>and so on it goes I have 74 functions , 12=
8 tables=20
and 34 views.&nbsp; With these and the other simular errors on the dbschema=
 sql=20
file I urgently need some guidance on how to fix these problems.</FONT></DI=
V>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>thanks</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Richard</FONT></DIV></BODY></HTML>

------=_NextPart_000_0015_01C37F70.02CA8F80--