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

Index: doc/src/sgml/plpgsql.sgml
================================================== =================
RCS file: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.67
diff -c -c -r1.67 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 19 Apr 2005 03:55:43 -0000 1.67
--- doc/src/sgml/plpgsql.sgml 26 May 2005 00:10:59 -0000
***************
*** 2007,2018 ****
</indexterm>

<para>
! By default, any error occurring in a <application>PL/pgSQL</>
! function aborts execution of the function, and indeed of the
! surrounding transaction as well. You can trap errors and recover
! from them by using a <command>BEGIN</> block with an
! <literal>EXCEPTION</> clause. The syntax is an extension of the
! normal syntax for a <command>BEGIN</> block:

<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
--- 2007,2019 ----
</indexterm>

<para>
! Any error occurring in <application>PL/pgSQL</> sets variables
! <varname>SQLSTATE</> and <varname>SQLERRM</>, and, by default,
! aborts execution of the function, and indeed of the surrounding
! transaction as well. You can trap errors and recover from them by
! using a <command>BEGIN</> block with an <literal>EXCEPTION</>
! clause. The syntax is an extension of the normal syntax for a
! <command>BEGIN</> block:

<synopsis>
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
Index: src/pl/plpgsql/src/gram.y
================================================== =================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.69
diff -c -c -r1.69 gram.y
*** src/pl/plpgsql/src/gram.y 7 Apr 2005 14:53:04 -0000 1.69
--- src/pl/plpgsql/src/gram.y 26 May 2005 00:11:06 -0000
***************
*** 80,85 ****
--- 80,90 ----
int n_initvars;
int *initvarnos;
} declhdr;
+ struct
+ {
+ int sqlstate_varno;
+ int sqlerrm_varno;
+ } fict_vars;
List *list;
PLpgSQL_type *dtype;
PLpgSQL_datum *scalar; /* a VAR, RECFIELD, or TRIGARG */
***************
*** 96,101 ****
--- 101,107 ----
PLpgSQL_diag_item *diagitem;
}

+ %type <fict_vars> fict_vars_sect
%type <declhdr> decl_sect
%type <varname> decl_varname
%type <str> decl_renname
***************
*** 244,262 ****
| ';'
;

! pl_block : decl_sect K_BEGIN lno proc_sect exception_sect K_END
{
PLpgSQL_stmt_block *new;

new = palloc0(sizeof(PLpgSQL_stmt_block));

new->cmd_type = PLPGSQL_STMT_BLOCK;
! new->lineno = $3;
new->label = $1.label;
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
! new->body = $4;
! new->exceptions = $5;

plpgsql_ns_pop();

--- 250,271 ----
| ';'
;

! pl_block : decl_sect fict_vars_sect K_BEGIN lno proc_sect exception_sect K_END
{
PLpgSQL_stmt_block *new;

new = palloc0(sizeof(PLpgSQL_stmt_block));

new->cmd_type = PLPGSQL_STMT_BLOCK;
! new->lineno = $4;
new->label = $1.label;
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
! new->body = $5;
! new->exceptions = $6;
!
! new->sqlstate_varno = $2.sqlstate_varno;
! new->sqlerrm_varno = $2.sqlerrm_varno;

plpgsql_ns_pop();

***************
*** 264,269 ****
--- 273,291 ----
}
;

+ fict_vars_sect :
+ {
+ PLpgSQL_variable *var;
+
+ plpgsql_ns_setlocal(false);
+ var = plpgsql_build_variable("sqlstate", 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
+ $$.sqlstate_varno = var->dno;
+ var = plpgsql_build_variable("sqlerrm", 0,
+ plpgsql_build_datatype(TEXTOID, -1), true);
+ $$.sqlerrm_varno = var->dno;
+ plpgsql_add_initdatums(NULL);
+ };

decl_sect : opt_label
{
Index: src/pl/plpgsql/src/pl_exec.c
================================================== =================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.138
diff -c -c -r1.138 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 6 May 2005 17:24:55 -0000 1.138
--- src/pl/plpgsql/src/pl_exec.c 26 May 2005 00:11:08 -0000
***************
*** 180,185 ****
--- 180,186 ----
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
+ static char *unpack_sql_state(int ssval);


/* ----------
***************
*** 747,752 ****
--- 748,767 ----
int i;
int n;

+
+ /* setup SQLSTATE and SQLERRM */
+ PLpgSQL_var *var;
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlstate_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("00000"));
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("Sucessful completion"));
+
/*
* First initialize all variables declared in this block
*/
***************
*** 855,860 ****
--- 870,885 ----
RollbackAndReleaseCurrentSubTransaction();
MemoryContextSwitchTo(oldcontext);
CurrentResourceOwner = oldowner;
+
+ /* set SQLSTATE and SQLERRM variables */
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlstate_varno]);
+ pfree((void *) (var->value));
+ var->value = DirectFunctionCall1(textin, CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+ pfree((void *) (var->value));
+ var->value = DirectFunctionCall1(textin, CStringGetDatum(edata->message));

/*
* If AtEOSubXact_SPI() popped any SPI context of the subxact,
***************
*** 919,924 ****
--- 944,969 ----
return PLPGSQL_RC_OK;
}

+ /*
+ * unpack MAKE_SQLSTATE code
+ * This code is copied from backend/utils/error/elog.c.
+ */
+ static char *
+ unpack_sql_state(int ssval)
+ {
+ static char tbuf[12];
+ int i;
+
+ for (i = 0; i < 5; i++)
+ {
+ tbuf[i] = PGUNSIXBIT(ssval);
+ ssval >>= 6;
+ }
+ tbuf[i] = '\0';
+ return tbuf;
+ }
+
+

/* ----------
* exec_stmts Iterate over a list of statements
Index: src/pl/plpgsql/src/plpgsql.h
================================================== =================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.58
diff -c -c -r1.58 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 5 Apr 2005 06:22:16 -0000 1.58
--- src/pl/plpgsql/src/plpgsql.h 26 May 2005 00:11:08 -0000
***************
*** 336,344 ****
int lineno;
char *label;
List *body; /* List of statements */
! List *exceptions; /* List of WHEN clauses */
! int n_initvars;
! int *initvarnos;
} PLpgSQL_stmt_block;


--- 336,346 ----
int lineno;
char *label;
List *body; /* List of statements */
! List *exceptions; /* List of WHEN clauses */
! int n_initvars;
! int *initvarnos;
! int sqlstate_varno;
! int sqlerrm_varno;
} PLpgSQL_stmt_block;


Index: src/test/regress/expected/plpgsql.out
================================================== =================
RCS file: /cvsroot/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.28
diff -c -c -r1.28 plpgsql.out
*** src/test/regress/expected/plpgsql.out 7 Apr 2005 14:53:04 -0000 1.28
--- src/test/regress/expected/plpgsql.out 26 May 2005 00:11:10 -0000
***************
*** 2380,2382 ****
--- 2380,2408 ----
CONTEXT: PL/pgSQL function "missing_return_expr"
drop function void_return_expr();
drop function missing_return_expr();
+ -- test SQLSTATE and SQLERRM
+ create or replace function trap_exceptions() returns void as $_$
+ begin
+ begin
+ raise exception 'first exception';
+ exception when others then
+ raise notice '% %', SQLSTATE, SQLERRM;
+ end;
+ raise notice '% %', SQLSTATE, SQLERRM;
+ begin
+ raise exception 'last exception';
+ exception when others then
+ raise notice '% %', SQLSTATE, SQLERRM;
+ end;
+ return;
+ end; $_$ language plpgsql;
+ select trap_exceptions();
+ NOTICE: P0001 first exception
+ NOTICE: 00000 Sucessful completion
+ NOTICE: P0001 last exception
+ trap_exceptions
+ -----------------
+
+ (1 row)
+
+ drop function trap_exceptions();
Index: src/test/regress/sql/plpgsql.sql
================================================== =================
RCS file: /cvsroot/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.23
diff -c -c -r1.23 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql 7 Apr 2005 14:53:04 -0000 1.23
--- src/test/regress/sql/plpgsql.sql 26 May 2005 00:11:11 -0000
***************
*** 2018,2020 ****
--- 2018,2040 ----

drop function void_return_expr();
drop function missing_return_expr();
+ -- test SQLSTATE and SQLERRM
+ create or replace function trap_exceptions() returns void as $_$
+ begin
+ begin
+ raise exception 'first exception';
+ exception when others then
+ raise notice '% %', SQLSTATE, SQLERRM;
+ end;
+ raise notice '% %', SQLSTATE, SQLERRM;
+ begin
+ raise exception 'last exception';
+ exception when others then
+ raise notice '% %', SQLSTATE, SQLERRM;
+ end;
+ return;
+ end; $_$ language plpgsql;
+
+ select trap_exceptions();
+
+ drop function trap_exceptions();


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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

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

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

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

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

> 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

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
[mailtogsql-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



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org