Table of Contents

About

This howto shows you how to create and execute a SQL Procedure in Postgres.

This example has been taken from the official documentation - Examples

Steps

Creation of the procedure

The Create Statements

The script procedure-create-tbl-procedure.sql contains SQL DDL statements that will create:

The content is: 

tabul data print --type text --pipe-mode postgres/procedure-create-tbl-procedure.sql@howto

-- creation of the table used by the procedure
create table if not exists tbl
(
  num int
);

-- creation of the procedure
CREATE OR REPLACE PROCEDURE insert_data(a integer, b integer)
  LANGUAGE SQL
AS
$$
INSERT INTO tbl
VALUES (a);
INSERT INTO tbl
VALUES (b);
$$;

Execution of the Create Statements

We will execute it by: 

tabul data print '(postgres/procedure-create-tbl-procedure.sql@howto)@postgres'
# The quotes are only mandatory in bash because parenthesis are a bash token (ie subshell)

(postgres/procedure-create-tbl-procedure.sql@howto)@postgres
id   count   statement                                            line   error_code   error_message
--   -----   --------------------------------------------------   ----   ----------   -------------
 1       0   create table if not exists tbl\n(\n  num int\n)         2            0                
 2       0   CREATE OR REPLACE PROCEDURE insert_data(a integer,      8            0

Literal Execution of the Procedure

In this example, we show you how to call the procedure with the argument passed as literal.

The literal statement

The statement is: 

tabul data print --type text --pipe-mode postgres/procedure-call-literal.sql@howto

CALL insert_data(1, 2);

Execution of the literal statement

We will execute it by: 

tabul data print '(postgres/procedure-call-literal.sql@howto)@postgres'
# The quotes are only mandatory in bash because parenthesis are a bash token (ie subshell)

(postgres/procedure-call-literal.sql@howto)@postgres
id   count   statement                   line   error_code   error_message
--   -----   -------------------------   ----   ----------   -------------
 1      -1   CALL insert_data(1, 2);\n      1            0

Validation Check

After execution, you can validate the execution

  • visually by selecting the tbl table and checking the presence of the values 1 and 2
tabul data print tbl@postgres

tbl@postgres
num
---
  1
  2
  • automatically by executing a diff
tabul data diff postgres/procedure-call-literal-expected.csv@howto tbl@postgres

Diff Summary Report
The data resources are equals.
from                                                 to             equals   record_count   change_count
--------------------------------------------------   ------------   ------   ------------   ------------
postgres/procedure-call-literal-expected.csv@howto   tbl@postgres   ✓                   2              0

Parametrized Execution of the Procedure

The parametrized statement

The parametrized statement to execute the insert_data sql procedure is: 

CALL insert_data(?, ?);

where:

The Request Data Resource

To make this statement executable, we need to create a sql request manifest so that we can define the parameters 

kind: sql-request
spec:
  # the runtime data uri
  # * takes the file (procedure-call-parametrized.sql)
  # * located in the same directory as this manifest (md)
  # * and execute it against (postgres)
  data-uri: (procedure-call-parametrized.sql@md)@postgres
  # the data definition
  data-def:
    parameters:
      # the parameter for the first placeholder
      - value: 1 # the value passed to the procedure
      # the parameter for the second placeholder
      - value: 2 # the value passed to the procedure

The execution

We will execute this manifest by printing it 

tabul data print postgres/procedure-call-parametrized--sql-request.yml@howto

Output: 

(procedure-call-parametrized.sql@md)@postgres
id   count   statement                   line   error_code   error_message
--   -----   -------------------------   ----   ----------   -------------
 1      -1   CALL insert_data(?, ?);\n      1            0

Validation Check

We can see the 4 records 

tabul data print tbl@postgres

tbl@postgres
num
---
  1
  2
  1
  2

Next

Learn: