[postgis-users] EXECUTE CREATE TABLE AS SELECT

Miller, Stephan smille12 at harris.com
Tue Feb 10 09:20:25 PST 2015


Remi –

Thanks.  I am trying to generate nodes I think based on endpoints.  A separate list of nodes does not exist at the onset of this procedure.  At least, that is my perception.

I included the following at the bottom of the function:

    END$$;'

    EXECUTE 'return ' || updatedtablename';'
End
execute 'INTO '|| updatedtablename ||';'
$BODY$

Is this what you had in mind?

Thanks,

Steve

From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Rémi Cura
Sent: Tuesday, February 10, 2015 10:47 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] EXECUTE CREATE TABLE AS SELECT

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<mailto: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<mailto: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/a400d774/attachment.html>


More information about the postgis-users mailing list