[postgis-users] shp2pgsql and schemas

[Seamus Thomas Carroll]

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?

Seamus

On Fri, 12 Dec 2003, David Blasby wrote:

> > ...
> > 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;
> 
> above the "EXECUTE" line, add  (change EXECUTE to RAISE NOTICE):
> 
> 
> RAISE NOTICE ''ALTER TABLE '' || quote_ident(schema_name) || ''.'' || 
> quote_ident(table_name) || '' ADD COLUMN '' || quote_ident(column_name) 
> || '' geometry '';
> 
> dave
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>