Try catch equivalent in Postgres

Question

I need to clear (remove special characters) and convert a text to a numeric. But, if it is not possible to perform the conversion, I need to save the error in a variable to later, along with other errors, be inserted into a tuple of a table.

CREATE OR REPLACE FUNCTION TEST(V1 TEXT, OUT ERRO TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS
$$
    DECLARE
        V2 NUMERIC;
        MSG TEXT;
BEGIN
        --"TRY"
            V2 := REPLACE(REGEXP_REPLACE(V1, '[^,.0-9]+', '', 'g'),',','.');
        --"CATCH"
        MSG = 'CONVERSION FAILED';
  SELECT MSG INTO ERRO;
END;
$$;

SELECT * FROM TEST('65,^%F,5');
--EXPECTED: 'CONVERSION FAILED'

SELECT * FROM TEST('65^%F,5');
--EXPECTED: 65.5

What happened to the %F? And is your definition of a failed conversion that the pattern matches nothing? — Laurenz Albe
– Laurenz Albe, Commented Nov 25, 2019 at 17:58
'% F' these are just special characters in the String. Regexp Removes Them.. — Katarina
– Katarina, Commented Nov 25, 2019 at 18:02

Belayer · Accepted Answer · 2019-11-26 00:55:13Z

4

Try...Catch is implemented in Postgres as a code block with exception handling. For this situation the error we're catching in can the resulting string from regex be converted to a numeric value. The following makes that conversion they converts back to a string to match the return value.

create or replace function test(v1 text)
returns text
language plpgsql
as
$$
declare 
    erro text ;
begin  

    begin     -- try 
        erro:= (replace(regexp_replace(v1, '[^,.0-9]+', '', 'g'),',','.')::numeric)::text;
    exception -- catch 
        when invalid_text_representation then -- error that can be handeled
             erro =  'CONVERSION FAILED';  
    end ; -- try..catch

    return erro; 
end;
$$;

answered Nov 26, 2019 at 0:55

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Katarina Over a year ago

Thank you! That's what I needed!

Jasen Over a year ago

the function block is also a begin-end for the purposes of exception traps, but using it as such here could be an over-simplification.

Collectives™ on Stack Overflow

Try catch equivalent in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related