On Sun, Oct 13, 2019 at 05:44:25PM +0000, McLaughlin, Michael wrote:
>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)
>
It's generally a good idea to mention which PostgreSQL version you're
on (I'll assume 12.0), and what the expected behavior is ...
Anyway, there's a couple of bugs in your code:
1) SQLSTATE is not an integer, it's a code describing the state. so you
have to change the err_num variable to text or something.
2) After fixing that, you'll get 0A000 sqlstate, which means "feature
not supported" [1] with the explanation that you've used unsupported
command - in this case savepoint. That's not supported in procedures,
unfortutately.
3) After removing the savepoint, you'll get another failure because the
exception block defines a subtransaction, which makes it impossible to
perform a commit in the procedure.
Unfortunately, these are limitations in the current implementation of
stored procedures. Some of that may be improved in future releases, but
at this point it's working as expected.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services