out parameter

1. Objective

IvorySQL provides Oracle-compatible OUT parameter functionality, including functions and procedures with OUT parameters, support for OUT parameters in anonymous blocks, and libpq support for OUT parameters.

This document aims to introduce the functionality of OUT parameters to users.

2. Function descriptions

IvorySQL provides Oracle-compatible OUT parameter functionality, including the following features.

2.1. functions with OUT parameters

Syntax：

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | { IMMUTABLE | STABLE | VOLATILE }
    | [ NOT ] LEAKPROOF
    | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...

The argmode can be IN, OUT, INOUT (IN OUT), or VARIADIC. If not specified, the default is IN. The argmode can also be written after the argname.

Unlike native PostgreSQL, Oracle-compatible OUT parameters are not associated with the return value data type. Neither IN OUT nor OUT modes can have default values. If there are OUT parameters and the function’s return type is not void, the function body must include a RETURN statement.

2.2. support for OUT parameters in anonymous blocks

Supports binding variables in the form of colon placeholders, such as :1, :name.

Added new DO+USING syntax： DO [ LANGUAGE lang_name ] code [USING IN | OUT | IN OUT, …]

Supports binding variables in libpq by position and by parameter name, providing the system function get_parameter_description, which returns the relationship between variable names and their positions based on the SQL statement.

2.3. calling functions with OUT parameters in libpq

The libpq interface provides functions for preparing, binding, and executing statements, which are similar to the corresponding OCI functions. The usage process is as follows:

Use IvyHandleAlloc to allocate statement and error handles. Call IvyStmtPrepare to prepare the statement. Call IvyBindByPos or IvyBindByName to bind parameters. Call IvyStmtExecute to execute, which can be repeated. Call IvyFreeHandle to release the statement and error handles.

Additionally, a series of interface functions have been implemented, including Ivyconnectdb, Ivystatus, Ivyexec, IvyresultStatus, IvyCreatePreparedStatement, IvybindOutParameterByPos, IvyexecPreparedStatement, IvyexecPreparedStatement2, Ivynfields, Ivyntuples, and Ivyclear.

Example programs can be found in the src/interfaces/libpq/ivytest directory of the source code.

3. test examples

3.1. functions with OUT parameters

OUT parameters are not associated with the return value data type

ivorysql=# create or replace function test_return_out(id integer,price out integer,name out varchar) return varchar
ivorysql-# as
ivorysql-# begin
ivorysql-#   price := 20000;
ivorysql-#   name := 'test a char out';
ivorysql-#   return 'welcome to QingDao';
ivorysql-# end;
ivorysql-# /
CREATE FUNCTION

Neither IN OUT nor OUT modes can have default values

ivorysql=# create or replace function test_return_inout(id integer,price in out integer default 100,name out varchar) return varchar
ivorysql-# as
ivorysql-# begin
ivorysql-#   price := 20000 + price;
ivorysql-#   name := 'this is a test';
ivorysql-# return 'welcome to QingDao';
ivorysql-# end;
ivorysql-# /
ERROR:  IN OUT formal parameters may have no default expressions

If there are OUT parameters and the function’s return type is not void, the function body must include a RETURN statement.

--if function's return type is non-void, the function body must has RETURN statement
--if there is no RETURN statement, the function can be created, but when it is called,
--an error is raised
ivorysql=# create or replace function f2(id integer,price out integer) return varchar
ivorysql-# as
ivorysql-# begin
ivorysql-#   price := 2;
ivorysql-# end;
ivorysql-# /
CREATE FUNCTION
ivorysql=# declare
ivorysql-#   a varchar(20);
ivorysql-#   b int;
ivorysql-# begin
ivorysql-#   a := f2(1, b);
ivorysql-# end;
ivorysql-# /
ERROR:  Function returned without value
CONTEXT:  PL/iSQL function f2(pg_catalog.int4,pg_catalog.int4) line 0 at RETURN
PL/iSQL function inline_code_block line 5 at assignment

3.2. support OUT parameters in anonymous block

Supports binding variables in the form of colon placeholders and new DO+USING syntax

ivorysql=# do $$
ivorysql$# declare
ivorysql$#   a int;
ivorysql$# begin
ivorysql$#   :x := 1;
ivorysql$#   :y := 2;
ivorysql$# end; $$ using out, out;
 $1 | $2
----+----
  1 |  2
(1 row)

system function get_parameter_descr()

ivorysql=# select * from get_parameter_description('insert into t values(:x,:y)');
 name  | position
-------+----------
 false |        0
 :x    |        1
 :y    |        2
(3 rows)