From: | Mark Wynter <mark(at)dimensionaledge(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem using a pl/pgsql function to populate a geometry column with x, y data |
Date: | 2012-07-24 14:39:52 |
Message-ID: | 2EF463D2-0A95-4A28-AE34-B6D3DA4EF51D@dimensionaledge.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-07-24 14:45:16 | Re: Odd corruption issue reported on dba.stackexchange.com, need advice |
Previous Message | Tom Lane | 2012-07-24 14:36:59 | Re: Roles with empty password (probably bug in libpq and in psql as well). |