Postgresql, return error with exception

Question

I'm trying to catch exception in a postgresql stored procedure. Without my "exception when other", my procedure is working. But don't with it.

This is my code :

create or replace function test(aaa varchar)
returns table (mmm integer, xxx integer)
as $body$
declare
    ccc             varchar(250);
    ddd     integer;
    xxx     integer;
    mmm         integer;
begin

    select 
        srv_int_id
    into
        mmm
    from
        srv_service
    where
        srv_var_nom = aaa;

    ddd := 1/0;

    xxx := test2(9000,'AAA');

    return query select 
        xxx, 
        mmm;

exception
    when others then
        select ccc = 'Erreur ' || sqlstate || ' dans la procedure test.sql';
        return query select 0,0;

end;
$body$ language plpgsql;

On Squirrel, I hava this error

Error: ERREUR: la requête n'a pas de destination pour les données résultantes Indice : Si vous voulez annuler les résultats d'un SELECT, utilisez PERFORM > à la place. Où : fonction PL/pgsql p_ech_mes_lire_info_message(character varying), ligne > 44 à instruction SQL SQLState: 42601 ErrorCode: 0

But I don't understand it. Do you have an idea ? Thanks.

Alfabravo · Accepted Answer · 2017-02-23 21:54:00Z

1

You are not using the result of the select in the exception block. The message clearly says that you must use PERFORM if you intend to run something ignoring the output (like a SELECT whose resultset you are not putting into any variable... unless that = means you want to use INTO ccc).

edited Feb 23, 2017 at 21:54

answered Feb 23, 2017 at 16:01

Alfabravo

7,5786 gold badges49 silver badges83 bronze badges

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

2 Comments

Roman Tkachuk Over a year ago

return query is a form of return that allow to return table (set of records)

Paladice Over a year ago

Thanks for your answer. It help me a lot.

Collectives™ on Stack Overflow

Postgresql, return error with exception

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related