postgresql - dynamic query postgres

Question

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

Answer 1

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 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.