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
There is the EXECUTE statement of plpgsql, which would do what you are trying to do - execute an SQL query string. You tagged dynamic, 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
EXECUTEstatement is not related to theEXECUTESQL statement supported by the PostgreSQL server. The server'sEXECUTEstatement 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.
From the fine manual:
Synopsis
EXECUTE name [ ( parameter [, ...] ) ]Description
EXECUTEis 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...
