CODEWITHSUNDEEP
sqlpostgresqlplpgsql
wfgeo
wfgeo

Reputation: 3098

EXECUTE statement syntax error in PostgreSQL

I am working on an extension of a PostgreSQL library that takes a string representation of a query as input. Basically I need to instantiate the resulting table that this string-based query produces, modify it, and then pass it to another function.

Right now I am just trying to get the query instatiated as a temporary table, so I am using this sample query:

CREATE TEMPORARY TABLE pgr_table (seq INTEGER, path_seq INTEGER, node INTEGER, edge BIGINT, cost DOUBLE PRECISION, agg_cost DOUBLE PRECISION);

EXECUTE 'SELECT gid AS id, source, target, cost, reverse_cost FROM ways;' INTO pgr_table;

But this results in a syntax error, just after the EXECUTE command. Am I not using it correctly?

By the way, I am aware of the dangers of SQL injection and using EXECUTE willy-nilly. The queries that I am making are not designed for front-end use, and I am following the design patterns already set forth by the library which I am modifying.

Upvotes: 2

Views: 4354

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

you confuse SQL execute and plpgsql execute - first to execute prepared statement and is run in SQL (as you try). second is a part of function plpgsql code

https://www.postgresql.org/docs/current/static/sql-execute.html

EXECUTE — execute a prepared statement

https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands (as discussed in Section 42.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:

examples:

t=# prepare s as select now();
PREPARE
t=# execute s;
              now
-------------------------------
 2017-12-14 12:47:28.844485+00
(1 row)

and plpgsql:

t=# do
$$
declare
 t text;
begin
execute 'select now()' into t;
raise info '%',t;
end;
$$
;
INFO:  2017-12-14 12:48:45.902768+00
DO

updtae

to avoid injection using dynamic code, use function format https://www.postgresql.org/docs/current/static/functions-string.html

Format arguments according to a format string. This function is similar to the C function sprintf.

Upvotes: 3

Related Questions