How to execute query within format() function in Postgres? can any one please guide me.
IF NOT EXISTS ( SELECT format ('select id
from '||some_table||' where emp_id
='||QUOTE_LITERAL(m_emp_id)||' )
) ;
Add a comment
|
2 Answers
You may combine EXECUTE FORMAT and an IF condition using GET DIAGNOSTICS
Here's an example which you can reuse with minimal changes.I have passed table name using %I identifier and parameterised argument ($1) for employee_id. This is safe against SQL Injection.LIMIT 1 is used since we are interested if at least one row exists. This will improve query performance and is equivalent(or efficient) to using EXISTS for huge datasets with multiple matching rows.
DO $$
DECLARE
some_table TEXT := 'employees';
m_emp_id INT := 100;
cnt INT;
BEGIN
EXECUTE format ('select 1
from %I where employee_id
= $1 LIMIT 1',some_table ) USING m_emp_id ;
GET DIAGNOSTICS cnt = ROW_COUNT;
IF cnt > 0
THEN
RAISE NOTICE 'FOUND';
ELSE
RAISE NOTICE 'NOT FOUND';
END IF;
END
$$;
Result
NOTICE: FOUND
DO
Comments
The @Kaushik Nayak reply is correct - I will try just little bit more explain this issue.
PLpgSQL knows two types of queries to database:
static (embedded) SQL - SQL is written directly to plpgsql code. Static SQL can be parametrized (can use a varible), but the parameters should be used as table or column identifier. The semantic (and execution plan) should be same every time.
dynamic SQL - this style of queries is similar to classic client side SQL - SQL is written as string expression (that is evaluated) in running time, and result of this string expression is evaluated as SQL query. There are not limits for parametrization, but there is a risk of SQL injections (parameters must be sanitized against SQL injection (good examples are in @Kaushik Nayak reply)). More, there is a overhead with every time replanning - so dynamic SQL should be used only when it is necessary. Dynamic SQL is processed by
EXECUTEcommand. The syntax ofIFcommands is:IF expression THEN statements; ... END IF;
You cannot to place into expression PLpgSQL statements. So this task should be divided to more steps:
EXECUTE format(...) USING m_emp_id;
GET DIAGNOSTICS rc = ROW_COUNT;
IF cnt > 0 THEN
...
END IF;
