Error in creating function

Поиск
Список
Период
Сортировка
От Yancho
Тема Error in creating function
Дата
Msg-id 9bc77711-a19f-4f9c-857e-351b8ff9128a@s19g2000prg.googlegroups.com
обсуждение исходный текст
Ответы Re: Error in creating function
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Andrus"
Дата:
Сообщение: Re: Avoid huge perfomance loss on string concatenation
Следующее
От: Paul Boddie
Дата:
Сообщение: Re: Deadlock when updating table partitions (and presumed solution)