EXECUTE statement syntax error in PostgreSQL

Question

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.

You can only use execute inside a PL/pgSQL function

user330315
– user330315

2017-12-14 12:44:51 +00:00
Commented Dec 14, 2017 at 12:44 — user330315
– user330315, Commented Dec 14, 2017 at 12:44
you confuse SQL execute and plpgsql execute.

Vao Tsun
– Vao Tsun

2017-12-14 12:44:51 +00:00
Commented Dec 14, 2017 at 12:44 — Vao Tsun
– Vao Tsun, Commented Dec 14, 2017 at 12:44

Vao Tsun · Accepted Answer · 2017-12-14 12:46:19Z

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.

Collectives™ on Stack Overflow

EXECUTE statement syntax error in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related