Error in creating function

From: Yancho <mpulis(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Error in creating function
Date: 2007-12-06 12:16:14
Message-ID: 9bc77711-a19f-4f9c-857e-351b8ff9128a@s19g2000prg.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Cedric Boudin 2007-12-06 12:38:31 Re: simple update on boolean
Previous Message Usama Dar 2007-12-06 12:15:10 Re: libpq messages language