[postgis-users] shp2pgsql and schemas

[David Blasby]

> ...
> 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