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 tbl table
- and the stored procedure.
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:
- making it a executable data uri (ie adding the execution connection)
- and printing it
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:
- making it a executable data uri (ie adding the execution connection)
- and printing it
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:
- call is the call postgres command that invokes a procedure
- insert_data is the procedure name
- (?, ?) is the procedure signature with 2 parameters placeholders
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: