Error in creating function
I am trying to create a function, which takes the nearest 3 hospitals
to a point making use of a PostGIS function), and then check each
hospital for the exact distance on roads (by making use of a pgRouting
function).
Below please find a copy of my function, and u can also find it
highlighted here : http://yancho.pastebin.com/f13cc045e
CREATE OR REPLACE FUNCTION near_hospital(text, integer, integer)
RETURNS integer AS
$BODY$
DECLARE
pojnt ALIAS FOR $1;
box ALIAS FOR $2;
dist ALIAS FOR $3;
distances RECORD;
nearest RECORD;
BEGIN
nearest.dist := 1000000000;
FOR distances IN
select astext(h.the_geom) as
hospital_location from hospitals h where
(
h.the_geom && expand (pointfromtext(pojnt),
100000) and
distance ( h.the_geom ,
pointfromtext(pojnt) ) < 150000
)
order by distance (h.the_geom ,
pointfromtext(pojnt)) ASC
limit 3;
LOOP
select INTO hospital gid, the_geom, length(the_geom) AS
dist from shootingstar_sp
( 'streets',
(
select s.gid from streets s, hospitals h
where
source = (
select
give_source(distances.hospital_location,100000,150000))
limit 1
)
,
(
select gid from streets where
target = (select give_target(pojnt,100000,150000))
limit 1
)
,
5000,
'length',
true,
true
);
IF hospital.dist < nearest.dist THEN
nearest.dist := hospital.dist;
nearest.gid := hospital.gid;
select INTO nearest name from hospital h
where h.gid = hospital.gid ;
END IF;
END LOOP;
RETURN nearest.gid;
END;
' language 'plpgsql';
The error being given by pgAdminIII is : unterminated dollar-quoted
string at or near "$BODY$" [then some garbled text] for $1;
Any help will be extremely appreciated!
Thanks and regards
Matthew
am Thu, dem 06.12.2007, um 4:16:14 -0800 mailte Yancho folgendes:
I am trying to create a function, which takes the nearest 3 hospitals
to a point making use of a PostGIS function), and then check each
hospital for the exact distance on roads (by making use of a pgRouting
function).Below please find a copy of my function, and u can also find it
highlighted here : http://yancho.pastebin.com/f13cc045eCREATE OR REPLACE FUNCTION near_hospital(text, integer, integer)
RETURNS integer AS
$BODY$
...
END;' language 'plpgsql';
The error being given by pgAdminIII is : unterminated dollar-quoted
string at or near "$BODY$" [then some garbled text] for $1;Any help will be extremely appreciated!
change the last line into:
$BODY$ language 'plpgsql';
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net