---json
{
    "aliases": [
        { "path": ":howto:postgres:call_proc" },
        { "path": ":howto:postgres:procedure_execution" }
    ],
    "page_id": "nozisi4mltyq4jlu9d04f"
}
---
====== How to execute a procedure with an OUT parameter in Postgres? ======


===== About =====
This ''howto'' shows you how to create and execute a [[:docs:system:database:sql_procedure|SQL Procedure]] with [[:docs:system:database:sql_parameterized_statement|parameters]] in [[:howto:postgres:postgres|Postgres]].


This example has been taken from the [[https://jdbc.postgresql.org/documentation/callproc/#example61calling-a-built-in-stored-function|official documentation - Calling a built-in stored function]]


===== Steps =====

This example will call the built-in stored function [[https://www.postgresql.org/docs/8.1/functions-string.html|upper]] to converts a string to uppercase.


==== The SQL statement ====

The [[:docs:system:database:sql_statement|SQL statement]] to execute a [[:docs:system:database:sql_procedure|sql procedure]] is:
<unit>
<file sql postgres/upper-call.sql>
{ ? = 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

</file>
</unit>
It's a [[:docs:system:database:sql_parameterized_statement|parameterized statement]] where:
  * ''{'' is the start character of a [[:docs:system:database:jdbc_escape|jdbc escape block]]
  * ''?'' is the first [[:docs:system:database:sql_parameterized_statement#placeholder|sql parameter placeholder]] where the result is stored
  * ''='' is the assignation token
  * ''call'' is the [[https://www.postgresql.org/docs/current/sql-call.html|call postgres command]] that invokes a procedure
  * ''upper( ? )'' is the [[https://www.postgresql.org/docs/8.1/functions-string.html|upper function]] with a second [[:docs:system:database:sql_parameterized_statement#placeholder|sql parameter placeholder]] for the input
  * ''}'' is the end character of a [[:docs:system:database:jdbc_escape|jdbc escape block]]


==== The Request Data Resource ====



To make this [[:docs:system:database:sql_statement|statement]] [[docs:resource:runtime|executable]], we need to create a [[:docs:resource:sql_request#manifest|sql request manifest]] so that we can define the [[:docs:system:database:sql_parameterized_statement#parameters|parameters]]


<unit>
<file yml postgres/upper-call--sql-request.yml>
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

</file>
</unit>

==== The execution ====

We will [[docs:resource:runtime#execution|execute]] this statement by [[:docs:tabul:data:print|printing]] it

<unit>
<code bash>
tabul data print postgres/upper-call--sql-request.yml@howto
</code>

Output:
<console>
(upper-call.sql@md)@postgres
para_1
----------------------
LOWERCASE TO UPPERCASE
</console>
</unit>


===== Next =====

Learn:
  * [[howto:postgres:procedure_call|how to execute a procedure]]
  * [[function_setof|how to return a result set from a function with Postgres]]