Code including test:
-- Transaction Management Example.
DROP PROCEDURE IF EXISTS testing;
-- Transaction Management Example.
CREATE OR REPLACE PROCEDURE testing
( IN pv_one VARCHAR(30)
, IN pv_two VARCHAR(10)) AS
$$
DECLARE
/* Declare error handling variables. */
err_num INTEGER;
err_msg INTEGER;
BEGIN
/* Create a SAVEPOINT as a starting point. */
SAVEPOINT starting_point;
/* Log actual parameter values. */
INSERT INTO msg VALUES (pv_one||'.'||pv_two);
/* Commit the series of inserts. */
COMMIT;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLSTATE;
err_msg := SUBSTR(SQLERRM,1,100);
RAISE NOTICE 'Trapped Error: %', err_msg;
END
$$ LANGUAGE plpgsql;
do $$
DECLARE
lv_one VARCHAR(30) := 'INDIVIDUAL';
lv_two VARCHAR(19) := 'R11-514-34';
BEGIN
RAISE NOTICE '[%]', lv_one;
RAISE NOTICE '[%]', lv_two;
CALL testing( pv_one := lv_one, pv_two := lv_two );
END
$$;
SELECT * FROM msg;
Running the code:
videodb=> \i twoflat.sql
DROP PROCEDURE
CREATE PROCEDURE
psql:twoflat.sql:58: NOTICE: [INDIVIDUAL]
psql:twoflat.sql:58: NOTICE: [R11-514-34]
psql:twoflat.sql:58: ERROR: invalid input syntax for integer: "0A000"
CONTEXT: PL/pgSQL function testing(character varying,character varying) line 17 at assignment
SQL statement "CALL testing( pv_one := lv_one, pv_two := lv_two )"
PL/pgSQL function inline_code_block line 8 at CALL
comment
---------
(0 rows)