Implementation of SQLSTATE and SQLERRM variables

Hi,

I have Postgres 8.0.1 on Solaris 9 Sparc machine running in
"usr/local/pgsql" directory.

I am trying to apply "sqlstate.diff" patch for SQLERRM and SQLSTATE for
plpgsql.

1. I have copied "sqlstate.diff" in
"postgresql-8.0.1.old/src/pl/plpgsql/src" directory.
2. Executing command:
patch -p4 < sqlstate.diff

Now it is asking for " File to patch:", here I am giving "gram.y" (I don't
know what it should be). After this it is giving the following error.

bash-2.03# patch -p4 < sqlstate.diff
Looks like a context diff to me...
File to patch: gram.y
Hunk #3 failed at line 244.
Malformed patch at line 93:
patch: Line must begin with '+ ', ' ', or '! '.
bash-2.03#
bash-2.03#

Kindly let me know what's wrong?

Please help me how to apply this patch.

Thanks
Dinesh Pandey

-----Original Message-----
Subject: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

Hello,

I updated patch to last changes plpgsql code. Patch contains changes for
gram.y, pl_exec.c, plpgsql.h, regress/sql/plpgsql.sql and
regress/output/plpgsql.out. I can't to write documenation, my english is
terrible.

This patch is implementation of variables SQLERRM and SQLSTATE for plpgsql
language. Variable SQLSTATE contains five chars PostgreSQL Error Code,
SQLERRM contains relevant message last catched exception. All variables
are attached to plpgsql_block and have local scope. Default values are
'00000' for SQLSTATE and 'Sucessful completion' for SQLERRM.

Best regards
Pavel Stehule

Patch applied. I added a documentation mention too.

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

Pavel Stehule wrote:

Hello,

I updated patch to last changes plpgsql code. Patch contains changes for
gram.y, pl_exec.c, plpgsql.h, regress/sql/plpgsql.sql and
regress/output/plpgsql.out. I can't to write documenation, my english is
terrible.

This patch is implementation of variables SQLERRM and SQLSTATE for plpgsql
language. Variable SQLSTATE contains five chars PostgreSQL Error Code,
SQLERRM contains relevant message last catched exception. All variables
are attached to plpgsql_block and have local scope. Default values are
'00000' for SQLSTATE and 'Sucessful completion' for SQLERRM.

Best 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

Bruce Momjian wrote:

Patch applied.

This patch still needs work.

I added a documentation mention too.

I think the docs need more than just "these variables are set when an
exception is raised".

The patch current resets SQLSTATE and SQLERRM whenever a new block is
entered. So:

create function trap_exceptions() returns void as $_$
begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLSTATE, SQLERRM;
begin
raise notice '% %', SQLSTATE, SQLERRM;
end;
end;
return;
end; $_$ language plpgsql;

The second RAISE will report "successful completion". Is this the
behavior we want?

Is SQLERRM the best name for that variable? It seems a little obscure to me.

-Neil

Neil Conway <neilc@samurai.com> writes:

The second RAISE will report "successful completion". Is this the
behavior we want?

No, certainly not --- I was just griping about the same thing.

Is SQLERRM the best name for that variable? It seems a little obscure to me.

Oracle uses that name. We had an argument previously about whether we
should use an exactly Oracle-compatible name to deliver a definitely
not Oracle-compatible text string ...

regards, tom lane

I think the docs need more than just "these variables are set when an
exception is raised".

The patch current resets SQLSTATE and SQLERRM whenever a new block is
entered. So:

create function trap_exceptions() returns void as $_$
begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLSTATE, SQLERRM;
begin
raise notice '% %', SQLSTATE, SQLERRM;
end;
end;
return;
end; $_$ language plpgsql;

The second RAISE will report "successful completion". Is this the
behavior we want?

True, its foggy behave. Only if block contains exception than variables
SQLSTATE and SQLERRM are created.

Is SQLERRM the best name for that variable? It seems a little obscure to me.

This name use Oracle in PL/SQL. ESQL Oracle and DB2 too. I think so is
better use well knowed names for it than creating own. There is one
argument for it. PL/pgSQL is language very similar Oracle PL/SQL. PL/SQL
know SQLERRM, SQLCODE and SQLSTATE. An people which know PL/SQL will like
use this names in Pg.

Pavel Stehule

Is this implementation is available in postgres 8.0.2 ? Or which version of
Postgres?

Thanks
Dinesh Pandey

-----Original Message-----
From: pgsql-patches-owner@postgresql.org
[mailto:pgsql-patches-owner@postgresql.org] On Behalf Of Pavel Stehule
Sent: Thursday, May 26, 2005 12:47 PM
To: Neil Conway
Cc: Bruce Momjian; pgsql-patches@postgresql.org; 'Tom Lane'
Subject: Re: [PATCHES] Implementation of SQLSTATE and SQLERRM variables

I think the docs need more than just "these variables are set when an
exception is raised".

The patch current resets SQLSTATE and SQLERRM whenever a new block is
entered. So:

create function trap_exceptions() returns void as $_$ begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLSTATE, SQLERRM;
begin
raise notice '% %', SQLSTATE, SQLERRM;
end;
end;
return;
end; $_$ language plpgsql;

The second RAISE will report "successful completion". Is this the
behavior we want?

True, its foggy behave. Only if block contains exception than variables
SQLSTATE and SQLERRM are created.

Is SQLERRM the best name for that variable? It seems a little obscure to

me.

This name use Oracle in PL/SQL. ESQL Oracle and DB2 too. I think so is
better use well knowed names for it than creating own. There is one argument
for it. PL/pgSQL is language very similar Oracle PL/SQL. PL/SQL know
SQLERRM, SQLCODE and SQLSTATE. An people which know PL/SQL will like use
this names in Pg.

Pavel Stehule

-Neil

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Dinesh Pandey wrote:

Is this implementation is available in postgres 8.0.2 ? Or which
version of Postgres?

At this time, it's not available anywhere.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/