Error in creating function

Started by Matthew Pulisover 18 years ago2 messagesgeneral
Jump to latest
#1Matthew Pulis
mpulis@gmail.com

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

#2A. Kretschmer
andreas.kretschmer@schollglas.com
In reply to: Matthew Pulis (#1)
Re: Error in creating function

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/f13cc045e

CREATE 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