Thread: broken restore.sql script !?

broken restore.sql script !?

From
"Christian Sengstock"
Date:
hi list,

i have a auto-generated restore.sql script which doesn't work for me because of an obvious error inside of it. because the script is working for other people i thought of an os error (i use linux, others windows).

the problem:

The function is created outside of the 'geo' schema, but after that, the 'alter function' statement tries to alter the function inside of the 'geo' schema. the restore.sql file is from pgadmin. maybe there's a statement so that the function could be created inside of the geo schema without writing it explicit. like '\set search_path', so that i can restore it on the linux command line.


some lines of it: ...

------------------------------------------------------------------------------------------------------
--
-- Name: box2d_out(box2d); Type: FUNCTION; Schema: geo; Owner: postgres
--

CREATE FUNCTION box2d_out(box2d) RETURNS cstring
    AS '$libdir/liblwgeom.so', 'BOX2DFLOAT4_out'
    LANGUAGE c IMMUTABLE STRICT;

ALTER FUNCTION geo.box2d_out(box2d) OWNER TO postgres;

--
-- Name: box2d; Type: TYPE; Schema: geo; Owner: postgres
--

CREATE TYPE box2d (
    INTERNALLENGTH = 16,
    INPUT = box2d_in,
    OUTPUT = box2d_out,
    ALIGNMENT = int4,
    STORAGE = plain
);

ALTER TYPE geo.box2d OWNER TO postgres;
-----------------------------------------------------------------------------------------------------------------------


thankx,
chris

Re: broken restore.sql script !?

From
Tom Lane
Date:
"Christian Sengstock" <csengstock@gmail.com> writes:
> The function is created outside of the 'geo' schema, but after that, the
> 'alter function' statement tries to alter the function inside of the 'geo'
> schema.

This is pg_dump's normal method of operation.  There should have been a
"SET search_path = geo, pg_catalog;" command somewhere before the
excerpt you gave.  I think you probably broke the script by carelessly
extracting a part of it...

            regards, tom lane

Re: broken restore.sql script !?

From
"Christian Sengstock"
Date:
hi again,

2006/2/22, Tom Lane <tgl@sss.pgh.pa.us>:
"Christian Sengstock" <csengstock@gmail.com> writes:
> The function is created outside of the 'geo' schema, but after that, the
> 'alter function' statement tries to alter the function inside of the 'geo'
> schema.
 
 

This is pg_dump's normal method of operation.  There should have been a
"SET search_path = geo, pg_catalog;" command somewhere before the
excerpt you gave. 
 
does that mean a prior "SET search_path = geo, pg_catalog;" would create the function inside of the geo schema ? i have this setting but it's not working for me, despite the function is in the public schema.
 
i use /> psql -d mydb -U user -f restore.sql
 
to restore it. Could the behaviour be different when using 'pg_restore'  ?
 
thanx,
chris
 

 

Re: broken restore.sql script !?

From
Tom Lane
Date:
"Christian Sengstock" <csengstock@gmail.com> writes:
> does that mean a prior "SET search_path =3D geo, pg_catalog;" would create =
> the
> function inside of the geo schema ? i have this setting but it's not workin=
> g
> for me, despite the function is in the public schema.

> i use /> psql -d mydb -U user -f restore.sql

> to restore it. Could the behaviour be different when using 'pg_restore'  ?

Shouldn't be different.  Can you provide a test case?

            regards, tom lane