Calling a procedure with inout parameters from psql-CodePudding

Given the following table

CREATE TABLE dt_test.dt_integer (
    id serial NOT NULL,
    test_col integer,
    test_comment varchar,
    CONSTRAINT dt_integer_pk PRIMARY KEY ( id ) ) ;

and a procedure for inserting data

CREATE PROCEDURE dt_test.integer_insert (
    a_id inout int,
    a_integer in integer,
    a_comment in varchar,
    a_err inout varchar ( 200 ) )
SECURITY DEFINER
LANGUAGE plpgsql
AS $$
DECLARE

BEGIN

    WITH inserted AS (
        INSERT INTO dt_test.dt_integer (
                test_col,
                test_comment )
            VALUES (
                a_integer,
                a_comment )
            RETURNING id
    )
    SELECT id
        INTO a_id
        FROM inserted ;

END ;
$$ ;

Can/how can the procedure be called from psql? In Oracle, using sql-plus this would look like:

DECLARE
    a_err varchar2 ( 200 ) ;
    a_id number ;
    
BEGIN

    dt_test.integer_insert ( a_id, 13, 'A prime example', a_err ) ;

    dbms_output.put_line ( 'The new ID is: ' || a_id ) ;

END ;

(I'd hate to think the sql-plus can do something that psql can't)

CodePudding user response：

Well, you'd do the same in PL/pgSQL:

set client_min_messages=notice;

do
$$
declare
   a_err text;
   a_id int;
begin
   call dt_test.integer_insert(a_id, 13, 'A prime example', a_err);
   raise notice 'The new ID is: %', a_id;
end;
$$
;