[postgis-users] EXECUTE CREATE TABLE AS SELECT

[Rémi Cura]

Hey,
it seems over complicated if you just want to do snapping.
(snapping is  : ensure that for each edge the first and last point of the
edge geom are exactly the same as associated node geom.
)
I think I coded it somewhere, can't find it, should be few lines.

You get error because you can't use naked SELECT in plpgsql.
You need either to SELECT .. INTO a_variable, or to use
PERFORM.

Cheers,
Rémi-C

2015-02-10 16:17 GMT+01:00 Miller, Stephan <smille12 at harris.com>:

>  I am running the following configuration:
>
>
>
> "POSTGIS="2.0.6 r12554" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6
> March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6"
> LIBJSON="UNKNOWN" TOPOLOGY RASTER"
>
>
>
> I am trying to generalize a procedure for checking topological integrity
> (snapping endpoints of linestrings).  I created a function that returns a
> new table.
>
>
>
> create or replace function fgcm.hc_check_gaps_in_linear_topology(IN
> tablename text, IN cleantopo TEXT)
>
>     returns table (objectid integer, f_code char varying, topo_shape
> topogeometry) as
>
> $BODY$
>
> declare
>
>     updatedtablename text;
>
>
>
> begin
>
>
>
>     -- SELECT sde_set_current_version(10.2.1);
>
>
>
>     updatedtablename = 'updated' || tablename;
>
>
>
>     RAISE NOTICE 'The updated table is %', updatedtablename;
>
>     raise NOTICE 'The input table name is %', tablename;
>
>     raise NOTICE 'Cleantopo is %', cleantopo;
>
>
>
>     --CREATE TABLE updatedtablename AS SELECT objectid, f_code, shape FROM
> tablename;
>
>
>
>     --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid,
> f_code, shape  FROM ' || tablename;
>
>     -- RETURN QUERY per 9.2 Section 39.6.1 Returning from a function
>
>     --return QUERY
>
>     --execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid,
> f_code, shape  FROM ' || tablename || ' INTO ' || updatedtablename;
>
>     execute 'CREATE TABLE ' || updatedtablename || ' AS SELECT objectid,
> f_code, shape  FROM ' || tablename;
>
>
>
>     -- Drop the existing topology
>
>     SELECT topology.DropTopology('cleantopo');
>
>
>
>     -- Create a new topology
>
>     -- Note need to generalize the SRID calculation to select the best fit
> UTM zone base on longitude extents
>
>     SELECT topology.CreateTopology('cleantopo',32648, 0.000001, TRUE);
>
>
>
>     -- Add the new topo_shape column
>
>         Select topology.AddTopoGeometryColumn('cleantopo', 'fgcm',
> 'updatedtablename', 'topo_shape', 'LINESTRING');
>
>
>
>     SELECT topology.TopologySummary('cleantopo');
>
>
>
>     DO $$DECLARE r record;
>
>     BEGIN
>
>         FOR r IN SELECT objectid, f_code, shape, topo_shape FROM
> fgcm.updatedtablename LOOP
>
>             BEGIN
>
>                 raise NOTICE 'Loading % attempt with shape = % and
> topo_shape = %', r.objectid, r.shape, r.topo_shape;
>
>                 UPDATE fgcm.updatedtablename SET topo_shape =
> topology.toTopoGeom(ST_Transform(r.shape,32648), 'red_roads_topo', 1, 1.0)
>
>                     where objectid = r.objectid;
>
>                 raise NOTICE 'Object % after conversion from shape = % to
> topo_shape = %', r.objectid, (ST_AsText(r.shape)),
> (ST_AsText(r.topo_shape));
>
>             EXCEPTION
>
>                 WHEN OTHERS THEN
>
>                     RAISE WARNING 'Loading of record % failed: % %',
> r.objectid, SQLSTATE, SQLERRM;
>
>             END;
>
>         end loop;
>
>     END$$;
>
>
>
> End
>
> $BODY$
>
> language plpgsql volatile
>
> COST 100
>
> ROWS 2000;
>
> -- How do I get the function saved as an fgcm.hc function?
>
> Alter function fgcm.hc_check_gaps_in_linear_topology(text, text)
>
>     owner to fgcm;
>
>
>
> -- Try as follows:
>
>
>
> SELECT * FROM fgcm.hc_check_gaps_in_linear_topology('hydrographycrv_evw',
> 'hydrocleantopo');
>
>
>
>
>
> I am encountering the following error (ERROR:  query has no destination
> for result data) when I try to EXECUTE a CREATE TABLE AS command at line
> 35.  I have tried to add a RETURN QUERY  and adding INTO on the end of the
> command line.  Any other ideas or suggestions would be appreciated.
>
>
>
> NOTICE:  The updated table is updatedhydrographycrv_evw
>
> NOTICE:  The input table name is hydrographycrv_evw
>
> NOTICE:  Cleantopo is hydrocleantopo
>
> ERROR:  query has no destination for result data
>
> HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
>
> CONTEXT:  PL/pgSQL function hc_check_gaps_in_linear_topology(text,text)
> line 24 at SQL statement
>
>
>
> ********** Error **********
>
>
>
> ERROR: query has no destination for result data
>
> SQL state: 42601
>
> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
>
> Context: PL/pgSQL function hc_check_gaps_in_linear_topology(text,text)
> line 24 at SQL statement
>
>
>
> Steve
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150210/f17e2900/attachment.html>