[postgis-users] shp2pgsql and schemas

[strk]

I'd rather look at a working code :)

The problem with schema support is the policy to use to avoid
breaking compatibility between postgis versions.

Maybe Carl has a working proposal (I've read back in archives).

--strk;

carrolls wrote:
> I realize it does not work in the current version.  I have edited 
> AddGeometryColumn so that there is an extra explicit varchar arguement for 
> schema.
> 
> The problem I am having is that when I try to add the extra geometry 
> column using alter table I recieve an error that I dont think sould 
> happen.
> 
> >From email below I try to alter the table in AddGeometryColumn with line:
> input := ''ALTER TABLE '' || quote_ident(schema_name) || ''.'' || quote_ident(table_name) || '' ADD COLUMN '' || quote_ident(column_name) || '' geometry '';
> which results in 
> ALTER TABLE "schema_name"."table_name" ADD COLUMN "column_name" geometry
> 
> This is the correct form isnt it?
> When I copy the output of "input" and paste into pgsql the table is 
> altered correctly but not when I try loading a shp2pgsql output.  Note 
> that I have edited shp2pgsql to handle schemas.
> 
> If you guys want to look at the code I can send you my altered postgis.sql 
> and shp2pgsql.c.
> 
> Seamus
> 
> On Mon, 15 Dec 2003, strk wrote:
> 
> > 
> > Schema handling is not supported by current postgis version.
> > I'd suggest you  set your search_path so to have the schema you'd
> > like the table to be created in as the first listed.
> > Using schema.table will not work when fed to AddGeometryColumn.
> > 
> > --strk;
> > 
> > 
> > carrolls wrote:
> > > The updated code is:
> > > input := ''ALTER TABLE '' || quote_ident(schema_name) || ''.'' || quote_ident(table_name) || '' ADD COLUMN '' || quote_ident(column_name) || '' geometry '';
> > > RAISE NOTICE ''input: %'', input;
> > > EXECUTE input;
> > > RAISE NOTICE exec finished;
> > > 
> > > And the new output is:
> > > psql:toload.txt:2: ERROR:  syntax error at or near "exec"
> > > CONTEXT:  compile of PL/pgSQL function "addgeometrycolumn" near line 43
> > > 
> > > No output is given for the first RAISE NOTICE.  The the_geom column is 
> > > still not added to the created table.
> > > 
> > > Seamus
> > > 
> > > On Fri, 12 Dec 2003, David Blasby wrote:
> > > 
> > > > Seamus Thomas Carroll wrote:
> > > > > I have changed the code to:
> > > > > input := ''ALTER TABLE '' || quote_ident(schema_name) || ''.'' || quote_ident(table_name) || '' ADD COLUMN '' || quote_ident(column_name) || '' geometry '';
> > > > > RAISE NOTICE ''input: %'', input;
> > > > > EXECUTE input;
> > > > > 
> > > > > and the output is now:
> > > > > psql:toload.txt:2: NOTICE:  input: ALTER TABLE roads.carrolls_route_alberta_test ADD COLUMN the_geom geometry
> > > > > psql:toload.txt:2: ERROR:  syntax error at or near "$3" at character 70
> > > > > CONTEXT:  PL/pgSQL function "addgeometrycolumn" line 69 at return
> > > > > 
> > > > > if I copy the result of the notice and paste it into the psql prompt it 
> > > > > works without error.
> > > > > 
> > > > > Could this be a error in posgtres that is causing a syntax error when one 
> > > > > is not present?
> > > > 
> > > > Try putting a "RAISE NOTICE exec finished" immediately after the EXECUTE.
> > > > 
> > > > dave
> > > > 
> > > > 
> > > > _______________________________________________
> > > > 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
> > 
> > _______________________________________________
> > 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