Postgres Pro
Downloads
Home   >   mailing lists

Thread: Intentionally produce Errors

Intentionally produce Errors

From
Markus Schiltknecht
Date:
Hi,

this is sort of a silly question, but: what's the proper way to
intentionally generate an error? I'm writing tests for pyPgSQL and want
to check it's error handling. Currently, I'm using:

SELECT "THIS PRODUCES AN SQL ERROR";

Is there any better way to generate errors? Probably even generating
specific errors given?

Regards

Markus

Re: Intentionally produce Errors

From
Matthias.Pitzl@izb.de
Date:
In PL/pgSQL you could use the RAISE command:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-errors-and-messages.h
tml

Best regards,
Matthias

> -----Original Message-----
>
> Hi,
>
> this is sort of a silly question, but: what's the proper way to
> intentionally generate an error? I'm writing tests for
> pyPgSQL and want
> to check it's error handling. Currently, I'm using:
>
> SELECT "THIS PRODUCES AN SQL ERROR";
>
> Is there any better way to generate errors? Probably even generating
> specific errors given?
>
> Regards
>
> Markus
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: Intentionally produce Errors

From
Markus Schiltknecht
Date:
Hello Matthias,

Matthias.Pitzl@izb.de wrote:
> In PL/pgSQL you could use the RAISE command:
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-errors-and-messages.h
> tml

Thank you, good to know. Unfortunately I'm not in a PL/PgSQL function,
just a plain query. Some standard functions which invoke RAISE?

Regards

Markus

BTW: why did you add '.h' to the link? Did you mess around with C
headers a little too much recently :-)

Re: Intentionally produce Errors

From
Andreas Seltenreich
Date:
Markus Schiltknecht writes:

> Matthias.Pitzl@izb.de wrote:
>> In PL/pgSQL you could use the RAISE command:
>> http://www.postgresql.org/docs/8.1/interactive/plpgsql-errors-and-messages.h
>> tml
>
> Thank you, good to know. Unfortunately I'm not in a PL/PgSQL function,
> just a plain query. Some standard functions which invoke RAISE?

I don't think there is any.  Maybe wrapping `raise exception' with a
function would work for you?  I'm using the following function to
raise exceptions from plain sql.

--8<---------------cut here---------------start------------->8---
create function error(text) returns void as $$
       begin
               raise exception '%', $1;
       end
       $$ language plpgsql;
--8<---------------cut here---------------end--------------->8---

However,

,----[ (info "(postgres)Errors and Messages") ]
|    `RAISE EXCEPTION' presently always generates the same SQLSTATE code,
| `P0001', no matter what message it is invoked with.
`----

regards,
andreas

Re: Intentionally produce Errors

From
Alban Hertroys
Date:
Andreas Seltenreich wrote:
> Markus Schiltknecht writes:
>> Matthias.Pitzl@izb.de wrote:

> --8<---------------cut here---------------start------------->8---
> create function error(text) returns void as $$
>        begin
>                raise exception '%', $1;
>        end
>        $$ language plpgsql;
> --8<---------------cut here---------------end--------------->8---
>
> However,
>
> ,----[ (info "(postgres)Errors and Messages") ]
> |    `RAISE EXCEPTION' presently always generates the same SQLSTATE code,
> | `P0001', no matter what message it is invoked with.
> `----

That could be fixed by adding an error code to your function and putting
that in a recognizable place in your exception text. Not pretty, but it
should do the job.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //