What is Dynamic SQL?
Dynamic SQL explanations are removed by a series of characters entered when the program starts. With dynamic SQL, you can create a more powerful and flexible application, as the full text of SQL statements might not be known at the time of compilation. This dynamic code or part of SQL is either entered by the developer or created by the program itself but we must compromise a little in performance.
When to use Dynamic SQL?
Ans: Sometimes you may need to generate dynamic commands inside your PL/pgSQL functions or procedure, that is, commands which involve different tables and datatypes, and their presence in the queries is known only at the runtime. Then, in this case, you have to use dynamic SQL.
Also in some cases, if your static SQL statements cannot perform or in the case where you really don't know the exact SQL statements to be executed by function or procedure then you have to use dynamic SQL
Dynamic SQL vs Static SQL in Database Engine
|Static SQL ( Embedded SQL)||Dynamic SQL (Interactive SQL)|
|SQL Statements are compiled at compile time.||SQL Statements gets compiled only at run time.|
|Parsing SQL Statement, validating table/view/procedure, optimization, and generation of application plan is done during compilation.||Parsing SQL Statement, validating table/view/procedure, optimization, and generation of application plan are done during run time.|
|The performance is really fast.||Comparatively, performance is not that much good as of static SQL because all things are done at run time.|
|It is less flexible.||It is more flexible.|
You can generally generate dynamic SQL in 3 ways:
- using the format function. (My Best one)
- using string concatenation operator (||) pronounced as pipe
Note: Dynamic values that are to be inserted into the constructed query require careful handling because they may also contain quote characters.
Method 1: Using the format function
Before going directly into the example we must understand certain string format:
S formats the value of the argument as a simple string.
I treat the value of the argument as a SQL identifier and double-quote it if necessary.
L quotes the argument value as an SQL literal.
Before moving ahead, let's discuss SQL Identifier and SQL Literal in short
SQL Identifier: SQL Identifier is essentially a name of a database, table name, index name, schema name, constraint name, cursor name, trigger name or table column,view name. In dynamic SQL %I format is replaced by SQL Identifier.
SQL Literal:A literal is an explicit numerical, character, string, or boolean value which is not represented by an SQL identifier. There are different type of SQL literal.
Some are :
|String Literal||Eg. 'Hello! everyone'|
|Integer Literal||Eg. 45, 78, +89 , -465,6E5|
|Decimal Literal||Eg. 45.56|
|DateTime Literal||Eg. '5/20/2020' , TIMESTAMP '2020-05-20 12:01:01';|
|Character Literal||A' '%' '9' ' ' 'z' '('|
|Boolean Literal||true, false, null|
Now let's go with an example :
Example 1: Using Single String in generating Dynamic SQL
SELECT format('Hello! Welcome to my %s!', 'Blog') as msg;
|Hello! Welcome to my Blog|
Here, 'Blog' string is inserted dynamically in place of %s and you can send any string.
Note: For general simple string we are using '%s' format.
Example 2: Using Multiple String in generating Dynamic SQL
SELECT format('%s! Welcome to my %s! - %s','Hi','Blog','Ourtechroom') as msg;
|Hi! Welcome to my Blog! - Ourtechroom|
Here, First, %s is replaced by 'Hi', Second %s is replaced by 'Blog' and the Third %s is replaced by 'Ourtechroom'
Note: For general simple string we are using '%s' format.
Example 3: Using SQL Identifier in Dynamic SQL
SELECT format('INSERT INTO %I VALUES(%L)', 'tbl_test', 'test');
|INSERT INTO tbl_test VALUES('test')|
Here, First, %I is replaced by 'tbl_test' and %L is replaced by 'test'
Example 4: Using dynamic SQL inside PostgreSQL function
CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql( tbl_name text sorttype text, test_id integer) RETURNS TABLE() LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ BEGIN RETURN QUERY EXECUTE format('select be.id, from %I t where m.commodity_id = %s order by t.amount %s',tbl_name,test_id,sorttype); END; $BODY$;
When you run this function, then fn_test_dynamic_sql(text, text, integer) gets created.
Here, First, %I is replaced by 'any tablename passed in the first parameter in function', the second %s is replaced by anyid passed as second parameter in the function, the third %s is replaced by 'sorttype passed as third parameter function eg. desc or asc'.
Method 2: Using the quote indent function
The PostgreSQL quote_ident string function is used to make a given string with suitably double-quoted, so as it can be used as an valid identifier in an SQL statement string when required.
quote_ident('Hello World'); // "Hello World"
quote_ident('mytable'); // mytable
quote_ident('MyTable'); // MyTable
Note: String inside quote_ident is case sensitive.
Example 2: Actual Implementation in PostgreSQL function
CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql( newvalue text, colname text, keyvalue integer) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN execute 'update student set ' + quote_ident(colname) + ' = ' + quote_literal(newvalue) + ' where id = ' + quote_literal(keyvalue); END; $BODY$;
--select * from public.fn_test_dynamic_sql1('abc','name',1)
Here we are generating UPDATE dynamic SQL where colname, newvalue and keyvalue is passed to PostgreSQL function from outside world say somewhere from backend ex. C#, python. Then it creates queries at runtime. You can similarly generate select, insert, delete query using quote_ident and quote_literal.
Note: Expressions that contain values that must be literal strings in the constructed command must be passed through quote_literal. These functions take the appropriate steps to return the entered text enclosed in double or single quotation marks, respectively, with any embedded special characters duly executed.
Because quote_literal is marked as "STRICT", it will always return "null" when called by the "null" argument. In the above
For example, if newvalue or keyvalue were zero, the entire dynamic query string will be zero, resulting in an error
from performing. You can avoid your problem by using the quote_nullable function, which works in the same way as quote_literal except that when called from null the argument returns a null string.
So it's better to use quote_nullable if you do not want error for null cases.
CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql( newvalue text, colname text, keyvalue integer) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ BEGIN execute 'update student set ' + quote_ident(colname) + ' = ' + quote_nullable(newvalue) + ' where id = ' + quote_nullable(keyvalue); END; $BODY$;
Method 3: Using the string concatenation function (||)
CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql( tbl_name text sorttype text, test_id integer) RETURNS TABLE() LANGUAGE 'plpgsql' COST 100 VOLATILE ROWS 1000 AS $BODY$ BEGIN select ' be.id from ' || tbl_name || ' t where m.commodity_id = ' || sorttype || ' order by t.amount ' || test_id ||; END; $BODY$;
Using things methods for generating dynamic SQL is prone to SQL Injection.