Proc to return a row of OUT values

[papawou]

In postgresql documentation:

The mode of an argument: IN, INOUT, or VARIADIC. If omitted, the default is IN. (OUT arguments are currently not supported for procedures. Use INOUT instead.)

Procedures are considered like functions who returns nothing but postgresql let you returns something.

And pg-promise use by default querymask 'none' when using proc.

Why ?

[vitaly-t]

I'm not sure if output variables are at all supported by the driver here. If they are, then please provide a complete example, using a generic query for such a procedure.

[papawou]

Sorry, i'm not good english speaker and don't understand well what you are asking.

Here is an example:

CREATE OR REPLACE PROCEDURE test(INOUT output_value boolean)
LANGUAGE plpgsql AS $$
BEGIN
    output_value := true;
END;$$;

It's INOUT argument we must give a value this is why the null value in pg-promise functions parameters.

for preventing that we can set a default value in procedure definition
CREATE OR REPLACE PROCEDURE test(INOUT output_value boolean DEFAULT null)

db.proc("test", [ null ]) throw an error because query return a row.

QueryResultError {
    code: queryResultErrorCode.notEmpty
    message: "No return data was expected."
    received: 1
    query: "call test(null)"
}

Following the documentation, it's normal behavior, proc use
none('CALL $1:alias($2:csv)', [procName, values])

For getting procedure working with INOUT arguments we need change querymask to accept at least one row retrieved
db.oneOrNone('CALL $1:alias($2:csv)', ["test", [ null ]]) return { output_value: true }

pg-promise seems to handle INOUT arguments (OUT currently not supported by postgresql).

sorry again for my english 🥖

[vitaly-t]

Ok, if the procedure can return one row like that, then the API will need to be revised.

[vitaly-t]

Updated functionality with this commit.

[vitaly-t]

The update has been released in v10.3.3.

@MendesKevin Give it a go, and let me know ;)