\! gs_ktool -g
GENERATE
1
DROP CLIENT MASTER KEY IF EXISTS anonymous_block_cmk CASCADE;
NOTICE:  client master key "anonymous_block_cmk" does not exist
CREATE CLIENT MASTER KEY anonymous_block_cmk WITH ( KEY_STORE = gs_ktool , KEY_PATH = "gs_ktool/1" , ALGORITHM =  SM4);
CREATE COLUMN ENCRYPTION KEY anonymous_block_cek WITH VALUES (CLIENT_MASTER_KEY = anonymous_block_cmk, ALGORITHM = SM4_SM3);
BEGIN
CREATE TABLE creditcard_info (id_number    int, name         text encrypted with (column_encryption_key = anonymous_block_cek, encryption_type = DETERMINISTIC),
credit_card  varchar(19) encrypted with (column_encryption_key = anonymous_block_cek, encryption_type = DETERMINISTIC));
END;
/
do $$
<<first_block>>
begin
insert into creditcard_info values(0, 'King', '123456');
end first_block $$;
select * from creditcard_info;
 id_number | name | credit_card 
-----------+------+-------------
         0 | King | 123456
(1 row)

delete from creditcard_info;
BEGIN
insert into creditcard_info values(1, 'Avi', '123456');
insert into creditcard_info values(2, 'Eli', '641245');
END;
/
select * from creditcard_info order by id_number;
 id_number | name | credit_card 
-----------+------+-------------
         1 | Avi  | 123456
         2 | Eli  | 641245
(2 rows)

delete from creditcard_info;
CREATE OR REPLACE PROCEDURE autonomous_1()  AS 
BEGIN
  insert into creditcard_info values(66, 66,66);
  commit;
  insert into creditcard_info values(77, 77,77);
  rollback;
END;
/
call autonomous_1();
 autonomous_1 
--------------
 
(1 row)

select * from creditcard_info order by id_number;
 id_number | name | credit_card 
-----------+------+-------------
        66 | 66   | 66
(1 row)

--success without return
CREATE OR REPLACE PROCEDURE exec_insert1 () AS
BEGIN
    insert into creditcard_info values(3, 'Rafi', '3');
    update creditcard_info set name='Sun' where credit_card = 3;
END;
/
call exec_insert1 ();
 exec_insert1 
--------------
 
(1 row)

--success  return void
CREATE or replace FUNCTION exec_insert2() RETURN void
AS
BEGIN 
   insert into creditcard_info values(4,'Gil',4);
   update creditcard_info set name='Joy' where credit_card = 4; 
END;
/
SELECT exec_insert2();
 exec_insert2 
--------------
 
(1 row)

call exec_insert2();
 exec_insert2 
--------------
 
(1 row)

--success RETURN integer
CREATE or replace FUNCTION exec_insert3() RETURN integer
AS
BEGIN 
   insert into creditcard_info values(5,'Peter',5);
   update creditcard_info set name= 'Xavier' where credit_card = 5; 
   return 1;
END;
/
SELECT exec_insert3();
 exec_insert3 
--------------
            1
(1 row)

call exec_insert3();
 exec_insert3 
--------------
            1
(1 row)

-- plpgsql IF 
CREATE or replace FUNCTION exec_insert4() RETURN void
AS
BEGIN
IF 2<5 THEN 
   insert into creditcard_info values(6,'Ziv',6);
   update creditcard_info set name='Peter' where credit_card = 6; 
END IF;
END;
/
SELECT exec_insert4();
 exec_insert4 
--------------
 
(1 row)

call exec_insert4();
 exec_insert4 
--------------
 
(1 row)

select * from creditcard_info order by id_number;
 id_number |  name  | credit_card 
-----------+--------+-------------
         3 | Sun    | 3
         4 | Joy    | 4
         4 | Joy    | 4
         5 | Xavier | 5
         5 | Xavier | 5
         6 | Peter  | 6
         6 | Peter  | 6
        66 | 66     | 66
(8 rows)

DROP TABLE creditcard_info;
DROP CLIENT MASTER KEY anonymous_block_cmk CASCADE;
NOTICE:  drop cascades to column encryption key: anonymous_block_cek
\! gs_ktool -d all
DELETE ALL
 1