CODEWITHSUNDEEP
sqlpostgispostgresql-9.4
Carlos Hernández
Carlos Hernández

Reputation: 41

Execution of a query in a function plpgsql (POSTGIS)

I'm looking for all the points that are inside a polygon. The query works for me, but when I add them to a function plggsql I get an error.

Query Ok

SELECT id, the_geom
FROM vialidad
WHERE ST_Contains('POLYGON((-99.1981315612793 19.357945,-99.1981315612793 19.376003,-99.161634 19.376003,-99.161634 19.357945,-99.1981315612793 19.357945))',   ST_AsText(the_geom));

When I add the query to the function plpgsql and the polygon that was created is in polygon variable.

EXECUTE 'SELECT id,the_geom FROM vialidad WHERE ST_Contains('||polygon||',ST_AsText(vialidad_cdmx_vertices_pgr.the_geom));'  
INTO nodes
USING polygon;

Console Error 

QUERY:  SELECT id, the_geom FROM vialidad WHERE ST_Contains(POLYGON((-99.1981315612793 19.357945,-99.1981315612793 19.376003,-99.161634 19.376003,-99.161634 19.357945,-99.1981315612793 19.357945)),ST_AsText(the_geom));

CONTEXT: PL/pgSQL function get_nodes_between_two_lines(integer,integer) line 37 at EXECUTE ********** Error **********

ERROR: syntax error at or near "19.357945" SQL state: 42601

Many thanks in advance for any possible help.

Upvotes: 0

Views: 171

Answers (2)

user330315
user330315

Reputation:

Do not concatenate strings into your SQL query. You are already passing the variable to the execute command with the using keyword, but your query doesn't use a place holder.

EXECUTE 'SELECT id,the_geom FROM vialidad WHERE ST_Contains($1,ST_AsText(vialidad_cdmx_vertices_pgr.the_geom))'  
--                                                          ^ here
INTO nodes
USING polygon;

The fact that you are selecting two columns but only supply a single target variable with the INTO clause is highly suspicious.

But I don't think you need dynamic SQL at all, assuming polygon is a variable, the following should work just fine:

SELECT id, the_geom 
  into id_var, geom_var
FROM vialidad 
WHERE ST_Contains(polygon,ST_AsText(vialidad_cdmx_vertices_pgr.the_geom))

Note that if you have a column named polygon in the table vialidad this is going to give you problems. You should rename your variable polygon then to have a different name. Many people simply put e.g. a l_ in front of the variable names, l_polygon or a p_ for parameters.

Upvotes: 1

lat long
lat long

Reputation: 930

Seems like when run query separately, there is single quote with the first argument of st_contains

ST_Contains('POLYGON((-99.1981315612793 19.357945,-99.1981315612793 19.376003,-99.161634 19.376003,-99.161634 19.357945,-99.1981315612793 19.357945))',   ST_AsText(the_geom));

But in console error there are no single quote around the polygon. You can try function with single quote inserted as

EXECUTE 'SELECT id,the_geom FROM vialidad WHERE ST_Contains('''||polygon||''',ST_AsText(vialidad_cdmx_vertices_pgr.the_geom));'  
INTO nodes
USING polygon;

Upvotes: 0

Related Questions