Thread: Create function errors

Create function errors

From
"Peck, Brian"
Date:

Hey all,

 

I’m trying to create functions for the calls I’m making to limit the number of DB pings I have to make (i.e. after they are all calls make one call that calls them in succession)  and I’m getting an error.

 

The function is

 

CREATE OR REPLACE FUNCTION nearestVertex(x1 double precision, y1 double precision,road_network character varying) RETURNS integer AS

$$

DECLARE

            result integer;

 

BEGIN

            SELECT source_id as vertex INTO result FROM $3 ORDER BY Distance(the_geom,PointFromText(POINT( $1 $2 ))) LIMIT 1;

 

            RETURN result;

END;

$$

LANGUAGE plpgsql;

 

And the error I get is

 

psql:D:/LMCO/AFUO/trunk/ASDFS_ODBC_2007_08_16/Scripts/PostgreSQL/functions.sql:12: ERROR:  syntax error at or near "$1"

LINE 1: SELECT source_id as vertex FROM  $1  ORDER BY Distance(the_g...

                                         ^

QUERY:  SELECT source_id as vertex FROM  $1  ORDER BY Distance(the_geom,PointFromText(POINT(  $2   $3  ))) LIMIT 1

CONTEXT:  SQL statement in PL/PgSQL function "nearestvertex" near line 5

 

I’m having trouble troubleshooting it since it’s query is not what should be the query actually being performed.

 

- Brian Peck

- 858-795-1398

- Software Engineer

- Lockheed Martin

 

Re: Create function errors

From
"Rodrigo De León"
Date:
On Dec 4, 2007 6:04 PM, Peck, Brian <brian.peck@lmco.com> wrote:

>             SELECT source_id as vertex INTO result FROM $3 ORDER BY
> Distance(the_geom,PointFromText(POINT( $1 $2 ))) LIMIT 1;

I think you're missing a comma, e.g. POINT( $1 , $2 ).