Dynamic column and tablename in sql statement postgresql | Dynamic query Postgresql


👤 Diwas Poudel    🕒 May 11 2020    📁 Fix    📜 0 comment

Here we will learn about Dynamic SQLIn addition, we will look at the actual purpose and use of dynamic PostgreSQL.
 

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 quote_ident and quote_literal functions 
  • 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 simple string.
%I
treat the value of the argument as SQL identifier and double-quote it if necessary. 
%L
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: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;

Output:

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;

Output:

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');

Output:

t
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$;

Output:

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.

Example 1:


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$;

Warning:

Using things methods for generating dynamic SQL is prone to SQL Injection.