Обсуждение: Error in creating function

Поиск
Список
Период
Сортировка

Error in creating function

От
Yancho
Дата:
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

Re: Error in creating function

От
"A. Kretschmer"
Дата:
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