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

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

Responses

Browse pgsql-general by date

  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).