Re: Restore problem

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 4:21 PM
To: pgsql-general(at)postgresql(dot)org
Cc: Bob Pawley
Subject: Re: [GENERAL] Restore problem

On Tuesday 28 December 2010 3:06:40 pm Bob Pawley wrote:
> Hi
>
> I have restored a database using psql to windows version 8.4.
>
> During the restore the trigger code became jumbled.
>
> I now have a great number of lines that have moved so that they are now
> included in lines the have been commented out – not to mention that the
> code is hard to read.

This is in the plain text dump file right?

>
> Is there some way of correcting this – or re restoring the database, so
> that I don’t have to go through the whole code line by line?

With out seeing an example that is going to be difficult :)

>
> Bob

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

This is the plain text dump file through pg_admin dump. But the plain text
dump fie through psql restored in the same way.

I don't have a copy of what it was but here is something quite similar to
the style I had before the dump.-

Begin Drop table if exists size ;
Drop table if exists temp_ ;

Drop table if exists temp1 ;

Drop table if exists target;

Create table size
( pro_id int4 ,
P_1 float,
P_2 float,
factor float
) ;

create table temp_
( pro_id int4 ,
graphic_id int4 ,
the_geom geometry,
ithe_geom geometry,
othe_geom geometry,
mthe_geom geometry,

ethe_geom geometry,
ip_target geometry,
op_target geometry
);

create table temp1
( id serial unique,
pro_id int4 ,
graphic_id int4 ,
the_geom geometry,
ithe_geom geometry,
othe_geom geometry,
mthe_geom geometry,
ethe_geom geometry,
ip_target geometry,
op_target geometry,
One varchar (5),
Two varchar (5),
Three varchar (5),
Four varchar (5)
);

Following is what it is now. Keep in mind email has word wrap.
(Note ---------1 is a comment out that, without word wrap, comments out a
long line of code.

DECLARE process_total integer ; processid integer ;
procgraphic cursor for select p_id.p_id.process_id

from p_id.p_id, processes_count where p_id.p_id.p_id_id =
processes_count.p_id_id

order by p_id.p_id.process_id; begin Select count
(p_id.p_id.process_id) INTO process_total FROM p_id.p_id,
processes_count Where p_id.p_id.p_id_id =
cesses_count.p_id_id; ------------------1 If process_total = 1
Then Open procgraphic; Fetch first from procgraphic
into processid;

Insert into target (process_id) values (processid) ;

Update p_id.p_id set proc_graphic_position = '1'
where p_id.p_id.process_id = processid;

Update p_id.p_id

set process_number = '1'

where p_id.p_id.process_id = processid;

Insert into size (P_1, P_2, pro_id) select
ST_area(st_envelope (graphics.spatial_ref.the_geom)), ST_area(st_envelope(
library.dgm_process.the_geom)),( processid) from graphics.spatial_ref,
library.dgm_process, p_id.p_id, processes_count where
graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_position and
p_id.p_id.process_id = processid and p_id.p_id.p_id_id =
processes_count.p_id_id and library.dgm_process.process_number =
p_id.p_id.process_graphic_id; Update size Set factor =
sqrt(P_1) / sqrt (P_2) / 3.0 where size.pro_id = processid; Insert
into temp_(the_geom, ithe_geom, othe_geom, mthe_geom, ethe_geom, ip_target,
op_target, pro_id, graphic_id) Select st_scale
(library.dgm_process.the_geom, size.factor, size.factor), st_scale
(library.dgm_process.ithe_geom, size.factor, size.factor), st_scale
(library.dgm_process.othe_geom, size.factor, size.factor), st_scale
(library.dgm_process.mthe_geom, size.factor, size.factor),

st_scale (library.dgm_process.ethe_geom, size.factor, size.factor),
st_scale (library.dgm_process.ip_target, size.factor, size.factor),
st_scale (library.dgm_process.op_target, size.factor, size.factor),
(processid), (p_id.p_id.process_graphic_id) from library.dgm_process,
graphics.spatial_ref, size, p_id.p_id Where
graphics.spatial_ref.position_ = p_id.p_id.proc_graphic_position and
p_id.p_id.process_id = size.pro_id and size.pro_id = processid and
library.dgm_process.process_number = p_id.p_id.process_graphic_id; insert
into temp1 (the_geom, ithe_geom, othe_geom, mthe_geom, ethe_geom, ip_target,
op_target, pro_id, graphic_id) select st_translate (temp_.the_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid
(temp_.the_geom)), st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))), st_translate (temp_.ithe_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid
(temp_.the_geom)), st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))), st_translate (temp_.othe_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid
(temp_.the_geom)), st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))), st_translate (temp_.mthe_geom,
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid
(temp_.the_geom)), st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))),

st_translate (temp_.ethe_geom,

st_x (st_centroid(graphics.spatial_ref.the_geom)) -

st_x (st_centroid (temp_.the_geom)),

st_y (st_centroid(graphics.spatial_ref.the_geom))-

st_y (st_centroid (temp_.the_geom))), st_translate (temp_.ip_target,
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid
(temp_.the_geom)), st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))), st_translate (temp_.op_target,
st_x (st_centroid(graphics.spatial_ref.the_geom)) - st_x (st_centroid
(temp_.the_geom)), st_y (st_centroid(graphics.spatial_ref.the_geom))-
st_y (st_centroid (temp_.the_geom))), (processid),
(p_id.p_id.process_graphic_id) from temp_, graphics.spatial_ref,
p_id.p_id, processes_count --select extent(the_geom) as the_geom from
temp_) as old_ref where temp_.pro_id = processid and
p_id.p_id.process_id = processid and graphics.spatial_ref.position_ =
p_id.p_id.proc_graphic_position and p_id.p_id.p_id_id =
processes_count.p_id_id;

--

Bob