About
This howto shows you how to create and execute a SQL Procedure with parameters in Postgres.
This example has been taken from the official documentation - Calling a built-in stored function
Steps
This example will call the built-in stored function upper to converts a string to uppercase.
The SQL statement
The SQL statement to execute a sql procedure is:
{ ? = call upper(?) }
-- To use an OUT parameter (ie the first ? parameter), the expression should be escaped (ie enclosed by { and })
-- Otherwise you get the error: This statement does not declare an OUT parameter. Use { ?= call ... } to declare one
It's a parameterized statement where:
- { is the start character of a jdbc escape block
- ? is the first sql parameter placeholder where the result is stored
- = is the assignation token
- call is the call postgres command that invokes a procedure
- upper( ? ) is the upper function with a second sql parameter placeholder for the input
- } is the end character of a jdbc escape block
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 executable data uri
# * takes the file (upper-call.sql)
# * located in the same directory as this manifest (md)
# * and execute it against (postgres)
data-uri: (upper-call.sql@md)@postgres
# the data definition
data-def:
parameters:
# the parameter for the first placeholder
- direction: out # retrieve the value
type: varchar # the type of the retrieved value
# the parameter for the second placeholder
- value: lowercase to uppercase # the value passed to the upper function
direction: in # set the value
type: varchar # the type of the value
The execution
We will execute this statement by printing it
tabul data print postgres/upper-call--sql-request.yml@howto
Output:
(upper-call.sql@md)@postgres
para_1
----------------------
LOWERCASE TO UPPERCASE
Next
Learn: