CODEWITHSUNDEEP
postgresqldynamic
indolentdeveloper
indolentdeveloper

Reputation: 1313

dynamic query postgres

I am new to postgres and running following dynamic query 

EXECUTE 'Select * from products';

I get following response.

ERROR: syntax error at or near "'Select * from products'" 
LINE 1: EXECUTE 'Select * from products';

I Know this would be something basic I m missing

Upvotes: 4

Views: 9896

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659197

There is the EXECUTE statement of plpgsql, which would do what you are trying to do - execute an SQL query string. You tagged , so this may be what you are looking for.

Only works inside plpgsql functions or DO statements (anonymous code blocks). The distinction between EXECUTE and SQL-EXECUTE made clear in the fine manual:

Note: The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL statement supported by the PostgreSQL server. The server's EXECUTE statement cannot be used directly within PL/pgSQL functions (and is not needed).

If you want to return values from a dynamic SELECT query as your example indicates, you need to create a function. DO statements always return void. More about returning values from a function in the very fine manual.

Upvotes: 3

mu is too short
mu is too short

Reputation: 434965

From the fine manual:

Synopsis

EXECUTE name [ ( parameter [, ...] ) ]

Description

EXECUTE is used to execute a previously prepared statement.

So EXECUTE doesn't execute a string of SQL, it execute a prepared statement that is identified by a name and you need to prepare the statement separately using PREPARE:

=> prepare stmt as select * from products;
=> execute stmt;
-- "select * from products" output goes here...

Upvotes: 2

Related Questions