Thread: SQLSTATE again

Hello

I changed implementation of SQLSTATE from block scope variables on
function scope variables. I am not sure if it's moust elegant solution,
but is very similar Oracle behavior (there is session scope variable)

CREATE OR REPLACE FUNCTION ff() RETURNS void AS $$
BEGIN
  RAISE NOTICE 'No exception: % %', SQLSTATE, SQLERRM;
  DECLARE x integer;
  BEGIN
    x := 10/0;
    EXCEPTION WHEN OTHERS THEN
    BEGIN
      RAISE NOTICE 'Div: % %', SQLSTATE, SQLERRM;
        BEGIN
          RAISE NOTICE 'Div2: % %', SQLSTATE, SQLERRM;
          RAISE EXCEPTION 'My user exception';
        EXCEPTION WHEN OTHERS THEN
          RAISE NOTICE 'User: % %', SQLSTATE, SQLERRM;
        END;
        RAISE NOTICE '1: % %', SQLSTATE, SQLERRM;
    END;
    RAISE NOTICE '2: % %', SQLSTATE, SQLERRM;
  END;
  RAISE NOTICE '3: % %', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
select ff();


NOTICE:  No exception: 00000 Successful completion
NOTICE:  Div: 22012 division by zero
NOTICE:  Div2: 22012 division by zero
NOTICE:  User: P0001 My user exception
NOTICE:  1: 00000 Successful completion
NOTICE:  2: 00000 Successful completion
NOTICE:  3: 00000 Successful completion

Any comments?

Regards
Pavel Stehule

Bruce Momjian wrote:
> Your patch has been added to the PostgreSQL unapplied patches list

This patch is not ready to be applied -- we're waiting for Pavel to
submit a revised version with the semantics described here:

http://archives.postgresql.org/pgsql-committers/2005-06/msg00017.php

-Neil

This patch is has been removed from the patch queue for the following
reason:

---------------------------------------------------------------------------

This patch is not ready to be applied -- we're waiting for Pavel to
submit a revised version with the semantics described here:

http://archives.postgresql.org/pgsql-committers/2005-06/msg00017.php

---------------------------------------------------------------------------


Pavel Stehule wrote:
> Hello
>
> I changed implementation of SQLSTATE from block scope variables on
> function scope variables. I am not sure if it's moust elegant solution,
> but is very similar Oracle behavior (there is session scope variable)
>
> CREATE OR REPLACE FUNCTION ff() RETURNS void AS $$
> BEGIN
>   RAISE NOTICE 'No exception: % %', SQLSTATE, SQLERRM;
>   DECLARE x integer;
>   BEGIN
>     x := 10/0;
>     EXCEPTION WHEN OTHERS THEN
>     BEGIN
>       RAISE NOTICE 'Div: % %', SQLSTATE, SQLERRM;
>         BEGIN
>           RAISE NOTICE 'Div2: % %', SQLSTATE, SQLERRM;
>           RAISE EXCEPTION 'My user exception';
>         EXCEPTION WHEN OTHERS THEN
>           RAISE NOTICE 'User: % %', SQLSTATE, SQLERRM;
>         END;
>         RAISE NOTICE '1: % %', SQLSTATE, SQLERRM;
>     END;
>     RAISE NOTICE '2: % %', SQLSTATE, SQLERRM;
>   END;
>   RAISE NOTICE '3: % %', SQLSTATE, SQLERRM;
> END;
> $$ LANGUAGE plpgsql;
> select ff();
>
>
> NOTICE:  No exception: 00000 Successful completion
> NOTICE:  Div: 22012 division by zero
> NOTICE:  Div2: 22012 division by zero
> NOTICE:  User: P0001 My user exception
> NOTICE:  1: 00000 Successful completion
> NOTICE:  2: 00000 Successful completion
> NOTICE:  3: 00000 Successful completion
>
> Any comments?
>
> Regards
> Pavel Stehule
>
>

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Hello

I have new version. I'll send it early.

Regards
Pavel Stehule

On Sat, 4 Jun 2005, Bruce Momjian wrote:

>
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
>     http://momjian.postgresql.org/cgi-bin/pgpatches
>
> It will be applied as soon as one of the PostgreSQL committers reviews
> and approves it.
>
> ---------------------------------------------------------------------------
>
>
> Pavel Stehule wrote:
> > Hello
> >
> > I changed implementation of SQLSTATE from block scope variables on
> > function scope variables. I am not sure if it's moust elegant solution,
> > but is very similar Oracle behavior (there is session scope variable)
> >
> > CREATE OR REPLACE FUNCTION ff() RETURNS void AS $$
> > BEGIN
> >   RAISE NOTICE 'No exception: % %', SQLSTATE, SQLERRM;
> >   DECLARE x integer;
> >   BEGIN
> >     x := 10/0;
> >     EXCEPTION WHEN OTHERS THEN
> >     BEGIN
> >       RAISE NOTICE 'Div: % %', SQLSTATE, SQLERRM;
> >         BEGIN
> >           RAISE NOTICE 'Div2: % %', SQLSTATE, SQLERRM;
> >           RAISE EXCEPTION 'My user exception';
> >         EXCEPTION WHEN OTHERS THEN
> >           RAISE NOTICE 'User: % %', SQLSTATE, SQLERRM;
> >         END;
> >         RAISE NOTICE '1: % %', SQLSTATE, SQLERRM;
> >     END;
> >     RAISE NOTICE '2: % %', SQLSTATE, SQLERRM;
> >   END;
> >   RAISE NOTICE '3: % %', SQLSTATE, SQLERRM;
> > END;
> > $$ LANGUAGE plpgsql;
> > select ff();
> >
> >
> > NOTICE:  No exception: 00000 Successful completion
> > NOTICE:  Div: 22012 division by zero
> > NOTICE:  Div2: 22012 division by zero
> > NOTICE:  User: P0001 My user exception
> > NOTICE:  1: 00000 Successful completion
> > NOTICE:  2: 00000 Successful completion
> > NOTICE:  3: 00000 Successful completion
> >
> > Any comments?
> >
> > Regards
> > Pavel Stehule
> >
> >
>
> Content-Description:
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
>
>