Problem using a pl/pgsql function to populate a geometry column with x,y data

[Mark Wynter <mark@xxxxxxxxxxxxxxxxxxx>]

Within a pl/pgsql function block, I'm wanting to populate a geometry column with x,y data.  However when I invoke the function, I get the error message that column "y" doesn't exist, even though it does.

The error message is:

SELECT load_xyz_data('/var/tmp/rscp_coverage_test.txt',32754);
ERROR:  column "y" does not exist
LINE 1: ...temp SET wkb_geometry = PointFromText(''POINT('|| y ||' '|| ...                                                          ^
QUERY:  SELECT 'UPDATE xyz_points_temp SET wkb_geometry = PointFromText(''POINT('|| y ||' '|| x ||')'','|| sourcesrid ||')'
CONTEXT:  PL/pgSQL function "load_xyz_data" line 24 at EXECUTE statement

My function is:

CREATE OR REPLACE FUNCTION load_xyz_data(sourcefile text, sourcesrid integer)
RETURNS text AS $$
DECLARE
BEGIN

EXECUTE 'CREATE TEMPORARY TABLE xyz_points_temp
(
x numeric,
y numeric,
z numeric
)
WITH (
OIDS=FALSE
)
ON COMMIT DROP';

-- Load xyz data
EXECUTE 'COPY xyz_points_temp FROM '|| quote_literal(sourcefile) ||' DELIMITER '',''';
-- Add geometry column
EXECUTE 'ALTER TABLE xyz_points_temp ADD COLUMN wkb_geometry geometry(POINT,'|| sourcesrid ||')';

-- Populate geometry column with x,y data
EXECUTE 'UPDATE xyz_points_temp SET wkb_geometry = ST_GeomFromText(''POINT('|| y ||' '|| x ||')'','|| sourcesrid ||')';

-- Now do something else

RETURN 'DATA LOADED';
END;
$$ LANGUAGE plpgsql STRICT;


I suspect it's a syntax issue, but can't get it to work?   Any suggestions?


Thanks





-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general