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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
