Thread: Create function errors

Create function errors

From

"Peck, Brian"

Date:

04 December 2007, 22:13:20

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:

05 December 2007, 00:26:19

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 ).