Exceptions in plpgsql

Question

Exceptions in plpgsql

Navigation

#1 by (1 votes)

0

see, I have this code in plpgsql:

CREATE OR REPLACE FUNCTION edatAlumne(int id_alumn) RETURNS int AS $$

    DECLARE

        edat integer;

    BEGIN

        select extract(year from age(current_date, naixement)) 
        into edat
        from alumnes
        where id_alumn = id

        return edat;
    EXCEPTION

        IF id_alumn = null THEN
            return null;


    END;
    $$ LANGUAGE plpgsql;

The statement says that I must create a function that must return the age of the student, identifying it through the ID (that part is done), but the problem comes when they mention the exception topic because:

The statement says that the function must return null if the parameter is null or the student does not exist, and that's where I'm lost, since I do not handle the exception issue very well, since I just started, some help?

postgresql plpgsql

asked by THR4SH3RP0L0 22.03.2017 в 18:27

source

1 answer

GoogleSignInApi does not allow me to login with a google account filter data in datagridview c # window form

score 1 · Accepted Answer

Let's see if I understood the question correctly.

The statement says that the function must return null if the parameter is null

To solve this point you simply have to ask if the parameter is null or not.

example.

if id_alumn is null then
    return null;

or the student does not exist

To verify if a student does not exist we must handle the NO_DATA_FOUND exception that is triggered when the query does not return data.

example.

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'Alumno inexistente!';

RAISE EXCEPTION generates an error that the user will visualize with the text you indicate.

The complete example is as follows.

CREATE OR REPLACE FUNCTION edatAlumne(int id_alumn) RETURNS int AS $$

DECLARE

    edat integer;

BEGIN

    if id_alumn is null then
        return null;
    else
        select extract(year from age(current_date, naixement)) 
        into edat
        from alumnes
        where id = id_alumn;

        return edat;
    end if;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'Alumno inexistente!';

END;
$$ LANGUAGE plpgsql;

I hope it helps. Greetings!