Re: Restore problem - Mailing list pgsql-general

-----Original Message-----
From: Adrian Klaver
Sent: Tuesday, December 28, 2010 4:21 PM
To: pgsql-general@postgresql.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.klaver@gmail.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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general