[postgis-users] EXECUTE CREATE TABLE AS SELECT

[Miller, Stephan]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150210/2dee7e67/attachment.html>