Execution of a query in a function plpgsql (POSTGIS)

Question

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.

have you tried passing the variable polygon between single quotes? EXECUTE 'SELECT id,the_geom FROM vialidad WHERE ST_Contains('||quote_literal(polygon)||', ...: assuming that polygon is a WKT literal. — Jim Jones
– Jim Jones, Commented Mar 25, 2018 at 14:45

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

lat long · Accepted Answer · 2018-03-26 06:23:59Z

0

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;

answered Mar 26, 2018 at 6:23

lat long

9306 silver badges16 bronze badges

Collectives™ on Stack Overflow

Execution of a query in a function plpgsql (POSTGIS)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related