[postgis-users] shp2pgsql and schemas

[Seamus Thomas Carroll]

Hi,

Things have changed a bit from 0.7.5 so the patches cant be applied 
directly but they are easy to follow to make the correct changes in the 
new code.  I have modified shp2pgsql.c and it works fine.  

I am having problems with postgis.sql.  I pass into AddGeometryColumn:
'uavmagis','roads','carrolls_route_alberta_test','the_geom','-1','MULTILINESTRING',2

Where AddGeometryColumn starts off as:
CREATE FUNCTION 
AddGeometryColumn(varchar,varchar,varchar,varchar,integer,varchar,integer)
	RETURNS text
	AS 
'
DECLARE
	database_name alias for $1;
	schema_name alias for $2;
	table_name alias for $3;
	column_name alias for $4;
...
EXECUTE ''ALTER TABLE '' || quote_ident(schema_name) || ''.'' || quote_ident(table_name) || '' ADD COLUMN '' || quote_ident(column_name) || '' geometry '';

The EXECUTE ''ALTER TABLE '' line gives me the syntax error:
psql:toload.txt:2: ERROR:  syntax error at or near "$3" at character 70
CONTEXT:  PL/pgSQL function "addgeometrycolumn" line 69 at return
 
when trying to load a file created by shp2pgsql.

Does anyone know what the error is or how to output the command that is 
being created from this line?  I have tried using echo but I cant get this 
to work.  For example, all I want sent to postgres given above input is:
ALTER TABLE roads.carrolls_route_alberta_test ADD COLUMN the_geom geometry;

Any advice would be appreciated,

Seamus


On Thu, 11 Dec 2003 aaronkoning at shaw.ca wrote:

> Hi Seamus,
> 
> This is actually a modified version that I created for the UNBC Datashare, since we wanted to use other schemas than public. There is a patch for postgis 0.7.5 on our pages under: development/code_db_method_doc.php#postgis_changes
> 
> Aaron Koning
> 
> ----- Original Message -----
> From: Seamus Thomas Carroll <carrolls at cpsc.ucalgary.ca>
> Date: Wednesday, December 10, 2003 3:05 pm
> Subject: [postgis-users] shp2pgsql and schemas
> 
> > Hi,
> > 
> > I have used shp2pgsql in a version released sometime before the 
> > summer and
> > there used to be an arguement for the schema and the table.
> > 
> > shp2pgsql for 0.8.0 does not have this option.  I thought I could just
> > input both piece of information in the table arguement ( 
> > schema.table )
> > but this results in the error when loading the file using psql:
> > NOTICE:  CREATE TABLE will create implicit sequence 
> > "carrolls_route_alberta_test_gid_seq" for "serial" column 
> > "carrolls_route_alberta_test.gid"psql:toload.sql:1: NOTICE:  
> > CREATE TABLE will create implicit sequence 
> > "carrolls_route_alberta_test_gid_seq" for "serial" column 
> > "carrolls_route_alberta_test.gid"CREATE TABLE
> > ERROR:  relation "roads.carrolls_route_alberta_test" does not exist
> > CONTEXT:  PL/pgSQL function "addgeometrycolumn" line 38 at execute 
> > statementpsql:toload.sql:2: ERROR:  relation 
> > "roads.carrolls_route_alberta_test" does not exist
> > CONTEXT:  PL/pgSQL function "addgeometrycolumn" line 38 at execute 
> > statementBEGIN
> > ERROR:  column "the_geom" of relation 
> > "carrolls_route_alberta_test" does not exist
> > psql:toload.sql:4: ERROR:  column "the_geom" of relation 
> > "carrolls_route_alberta_test" does not exist
> > ERROR:  current transaction is aborted, commands ignored until end 
> > of transaction block
> > ....
> > and so on.
> > 
> > What is the new technique for loading gis data into a schema other 
> > thanpublic?
> > 
> > Seamus
> > 
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> > 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>