Exception not running PostgreSQL

Question

I got the code:

DECLARE

BEGIN

    INSERT INTO articulo VALUES (33, 'Mesa 33', 1000.45463, 50.2345, 200.23459, 20, 'Conjunto');
    INSERT INTO articulo VALUES (34, 'Mesa 34', 300.4500, 15.2379 , 1.2379 , 5, 'kid 4');

    SELECT * FROM articulo;

    INSERT INTO cliente (id_clie, nom_clie, rfc_clie, tel_clie, dir_clie, suspendido) VALUES (44,'Rosa Almaran', 'R7XA-910101', '5544466677', 'sur 24', 0);

EXCEPTION
WHEN NO_DATA_FOUND THEN
  RAISE EXCEPTION ''All fields are required'';  
WHEN OTHERS THEN
RAISE EXCEPTION ''This is an exception'';   
END;

Which I read from here. When I try to run the code I get:

ERROR: syntax error at or near "INSERT"
LINE 5: INSERT INTO Product VALUES (33, 'Mesa 33', 1000.45463, 50 ....
        ^

I want PostgreSQL to show an exception depending of the type of exception it encounters.

Any help would be thanked!

The syntax error tells you that your query is broken, fix that and you should get the exception :) — Wolph
– Wolph, Commented Jan 18, 2015 at 23:01
The syntaxerror means that Postgres doesn't understand your query syntax somehow. My guess is that you are missing your function definition. — Wolph
– Wolph, Commented Jan 18, 2015 at 23:14
The problem is that I am not creating any function. I just want to see the Exceptions thrown. — Mike Pérez
– Mike Pérez, Commented Jan 18, 2015 at 23:16
This type of exception handling is specific to the PL/pgSQL context, since Postgres 9.0 you're not required to use a function anymore and can do it with a DO $$ <YOUR_CODE> $$ language 'plpgsql'; statement. — Wolph
– Wolph, Commented Jan 18, 2015 at 23:22

Wolph · Accepted Answer · 2015-01-19 00:07:31Z

1

Try wrapping your code in a DO statement:

DO LANGUAGE plpgsql $$
DECLARE

BEGIN

    INSERT INTO articulo VALUES (33, 'Mesa 33', 1000.45463, 50.2345, 200.23459, 20, 'Conjunto');
    INSERT INTO articulo VALUES (34, 'Mesa 34', 300.4500, 15.2379 , 1.2379 , 5, 'kid 4');

    SELECT * FROM articulo;

    INSERT INTO cliente (id_clie, nom_clie, rfc_clie, tel_clie, dir_clie, suspendido) VALUES (44,'Rosa Almaran', 'R7XA-910101', '5544466677', 'sur 24', 0);

EXCEPTION
WHEN NO_DATA_FOUND THEN
  RAISE EXCEPTION 'All fields are required';  
WHEN OTHERS THEN
RAISE EXCEPTION 'This is an exception';   
END;
$$;

edited Jan 19, 2015 at 0:07

answered Jan 18, 2015 at 23:24

Wolph

80.4k12 gold badges142 silver badges152 bronze badges

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

3 Comments

Mike Pérez Over a year ago

I used the code you provided but I keep having the same problem: ERROR: syntax error at or near "All" LINE 15: RAISE EXCEPTION '' All fields are required '; ^

Wolph Over a year ago

Just noticed that there's a syntax error there as well, it should just use ' instead of ''. I'll change it :)

Mike Pérez Over a year ago

Awesome. Finally got it running! Thanks a lot @Wolph

Collectives™ on Stack Overflow

Exception not running PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related