[postgis-users] Parse error - Invalid geometry in function

[Sylvain Racine]

Hello,

I wrote a function who looks up in an address table and records it if 
not. I have a problem with INSERT INTO statement. When I do it alone, it 
works fine. But if I use it inside a function (using variables) while I 
want to enter a new address, I got a 'parse error - invalid geometry'. 
Is anyone can help me?


CREATE TABLE adresses (
     id    integer    PRIMARY KEY DEFAULT nextval('seq_adresse'),
);

SELECT AddGeometryColumn('greffe1','adresses','geom', 4269, 'POINT', 2);



CREATE FUNCTION getCache(varchar)
	RETURNS SETOF integer AS '

	SELECT id FROM adresses WHERE adresse = $1;
' LANGUAGE sql;


CREATE FUNCTION getGeocode (varchar,varchar,varchar)
	RETURNS integer AS '
DECLARE
	_adresse ALIAS FOR $1;
	_lon ALIAS FOR $2;
	_lat ALIAS FOR $3;
	getCache RECORD;
BEGIN
	SELECT INTO getCache * FROM getCache(_adresse);
	IF NOT FOUND THEN
		INSERT INTO adresses(adresse,geom)
		  VALUES (_adresse, GeometryFromText(\'POINT(_lon _lat)\',4269));
		return currval(\'seq_adresse\');
	ELSE
		return getCache.getCache;

	END IF;
END;
' LANGUAGE plpgsql;


The error occurs while I do:

SELECT * FROM getGeocode ('20, RUE DE L\'ESCAULT, ST-ÉTIENNE, 
QC','-71.31453037261963','46.649259404968');

where this address not exists inside the "adresses" table.

But

INSERT INTO
adresses(adresse,geom)
VALUES ('20, RUE DE L\'ESCAULT, ST-ÉTIENNE, QC',
GeometryFromText('POINT(-71.31453037261963 46.649259404968)',4269));

works fine...



Best regards

Sylvain Racine