Table of Contents

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:

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: