PostgreSQL Dynamic Inline Code: Code Calls in Dynamic Stored Procedure – Client Stored Procedure Version Control

By Digoal

This article reviews PostgreSQL Dynamic Inline Code with examples.

Background

Stored procedures at the database-side provide complex business logic processing. Combined with the ACID feature of the database, placing the logic in the database for processing can improve reliability and reduce the number of interactions on the client.

Such a database performs well in certain scenarios requiring high reliability, consistency in transactions, and long logic, namely, excessive interactions with the database.

However, version control for the stored procedures of databases is weak. So, are there any solutions for the convenient management of these stored procedures?

Here are a few examples.

1. Version Control for Clients

For example, version control for the client can be achieved when the client submits stored procedure code with bound parameters at each request.

Input:

Inline code
Parameter

For example, run the following command:

    do language plpgsql $__$  
      declare  
        x text := %L;  
      begin  
        raise notice '%%', x;  
      end;  
    $__$  

hello digoal

Note: the client driver may not support parameter binding in inline code. The solution is to modify the client driver or encapsulate the function.

Format function constructs inline code with parameters as described on this page: https://www.postgresql.org/docs/12/functions-string.html#FUNCTIONS-STRING-FORMAT

For example, the following call is encapsulated in the function:

create or replace function dy1 (i text) returns void as $$  
declare  
  v text;  
begin  
  v := format($_$  
    do language plpgsql $__$  
      declare  
        x text := %L;  
      begin  
        raise notice '%%', x;  
      end;  
    $__$  
  $_$,  
  i  
  );  
  execute v;  
end;  
$$ language plpgsql strict;  
  
可行  
  
postgres=# select dy1('hello digoal');  
psql: NOTICE:  hello digoal  
 dy1   
-----  
   
(1 row)

Generic Encapsulation Examples

An insert statement is contained in dynamic code. The dependent tables are as shown below:

drop table if exists c;  
create table c (c1 int, c2 numeric, c3 timestamptz, c4 int[], c5 jsonb);

Create a dynamic function:

create or replace function dy2(inline text, VARIADIC para text[]) returns void as $$  
declare  
begin  
  case array_length(para,1)  
  when 1 then execute format(inline, para[1]);  
  when 2 then execute format(inline, para[1],para[2]);  
  when 3 then execute format(inline, para[1],para[2],para[3]);  
  when 4 then execute format(inline, para[1],para[2],para[3],para[4]);  
  when 5 then execute format(inline, para[1],para[2],para[3],para[4],para[5]);  
  else   
    raise exception 'must less then 5 parameters';  
  end case;  
end;  
$$ language plpgsql strict;

The input parameters are inline code and dynamic parameters.

select dy2(  
    $_$  
      do language plpgsql $$  
      declare  
        v1 int := %L;  
        v2 numeric := %L;  
        v3 timestamptz := %L;  
        v4 int[] := %L;  
        v5 jsonb := %L;  
      begin  
        insert into c values (v1,v2,v3,v4,v5);   
        raise notice '%%, %%, %%, %%, %%', v1,v2,v3,v4,v5;   
      end;  
      $$  
    $_$, -- inline code  
    '1',  -- 动态参数1  
    '1.1',  -- 动态参数2  
    now()::text,  -- 动态参数3  
    '{1,2,3,4}',  -- 动态参数4  
    '{"a":1,"b":{"k":"v"}}'   -- 动态参数5

Results:

psql: NOTICE:  1, 1.1, 2019-08-25 23:35:58.200068+08, {1,2,3,4}, {"a": 1, "b": {"k": "v"}}  
 dy2   
-----  
   
(1 row)  
  
postgres=# select * from c;  
 c1 | c2  |              c3               |    c4     |            c5               
----+-----+-------------------------------+-----------+---------------------------  
  1 | 1.1 | 2019-08-25 23:35:58.200068+08 | {1,2,3,4} | {"a": 1, "b": {"k": "v"}}  
(1 row)

If possible, there is a simpler dynamic encapsulation, but it requires the client to use the format function of the database when calling.

create or replace function dy3(inline text) returns void as $$  
declare  
begin  
  execute inline;  
end;  
$$ language plpgsql strict;

When calling, the dynamic parameters should be included:

select dy3(  
  format(  
    $_$  
      do language plpgsql $$  
      declare  
        v1 int := %L;  
        v2 numeric := %L;  
        v3 timestamptz := %L;  
        v4 int[] := %L;  
        v5 jsonb := %L;  
      begin  
        perform v1,v2,v3,v4,v5;   
        insert into c values (v1,v2,v3,v4,v5);   
        raise notice '%%, %%, %%, %%, %%', v1,v2,v3,v4,v5;   
      end;  
      $$  
    $_$, -- inline code  
    '1',  -- 动态参数1  
    '1.1',  -- 动态参数2  
    now()::text,  -- 动态参数3  
    '{1,2,3,4}',  -- 动态参数4  
    '{"a":1,"b":{"k":"v"}}'   -- 动态参数5  
  )  
);

Results:

psql: NOTICE:  1, 1.1, 2019-08-25 23:36:19.065442+08, {1,2,3,4}, {"a": 1, "b": {"k": "v"}}  
 dy3   
-----  
   
(1 row)  
  
postgres=# select * from c;  
 c1 | c2  |              c3               |    c4     |            c5               
----+-----+-------------------------------+-----------+---------------------------  
  1 | 1.1 | 2019-08-25 23:35:58.200068+08 | {1,2,3,4} | {"a": 1, "b": {"k": "v"}}  
  1 | 1.1 | 2019-08-25 23:36:19.065442+08 | {1,2,3,4} | {"a": 1, "b": {"k": "v"}}  
(2 rows)

2. Version Control at the Database-Side

The content of the stored procedure can also be stored in a table with dynamic calls.

postgres=# create table tcode(id int primary key, code text);  
CREATE TABLE  
  
-- 插入一段inline code  
postgres=# insert into tcode values (1,     $_$  
postgres$#       do language plpgsql $$  
postgres$#       declare  
postgres$#         v1 int := %L;  
postgres$#         v2 numeric := %L;  
postgres$#         v3 timestamptz := %L;  
postgres$#         v4 int[] := %L;  
postgres$#         v5 jsonb := %L;  
postgres$#       begin  
postgres$#         insert into c values (v1,v2,v3,v4,v5);   
postgres$#         raise notice '%%, %%, %%, %%, %%', v1,v2,v3,v4,v5;   
postgres$#       end;  
postgres$#       $$  
postgres$#     $_$);  
INSERT 0 1

Create a dynamic function that calls the inline code of the table.

create or replace function dy4(vid int, VARIADIC para text[]) returns void as $$  
declare  
  inline text;  
begin  
  select code into inline from tcode where id=vid;  
  case array_length(para,1)  
  when 1 then execute format(inline, para[1]);  
  when 2 then execute format(inline, para[1],para[2]);  
  when 3 then execute format(inline, para[1],para[2],para[3]);  
  when 4 then execute format(inline, para[1],para[2],para[3],para[4]);  
  when 5 then execute format(inline, para[1],para[2],para[3],para[4],para[5]);  
  else   
    raise exception 'must less then 5 parameters';  
  end case;  
end;  
$$ language plpgsql strict;

The call is listed below:

select dy4(1,  
    '1',   
    '1.1',   
    now()::text,   
    '{1,2,3,4}',   
    '{"a":1,"b":{"k":"v"}}'  
);

Results:

psql: NOTICE:  1, 1.1, 2019-08-25 23:48:06.863258+08, {1,2,3,4}, {"a": 1, "b": {"k": "v"}}  
 dy4   
-----  
   
(1 row)  
  
postgres=# select * from c;  
 c1 | c2  |              c3               |    c4     |            c5               
----+-----+-------------------------------+-----------+---------------------------  
  1 | 1.1 | 2019-08-25 23:35:58.200068+08 | {1,2,3,4} | {"a": 1, "b": {"k": "v"}}  
  1 | 1.1 | 2019-08-25 23:36:19.065442+08 | {1,2,3,4} | {"a": 1, "b": {"k": "v"}}  
  1 | 1.1 | 2019-08-25 23:48:06.863258+08 | {1,2,3,4} | {"a": 1, "b": {"k": "v"}}  
(3 rows)

References

Note: Injection needs to be prevented for dynamic SQL.

Community

PostgreSQL Dynamic Inline Code: Code Calls in Dynamic Stored Procedure – Client Stored Procedure Version Control

Background

1. Version Control for Clients

Generic Encapsulation Examples

2. Version Control at the Database-Side

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

AnalyticDB for PostgreSQL

Fraud Detection