Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables for
Bruce Momjian writes: > Guys, is this patch ready for application? I think so, but am not 100% > sure. I haven't read the code yet, but in any case I still object to choosing Oracle-like names for Oracle-incompatible functionality. We need to settle on better names. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables for
Hello,
I changed code by your and Neil's notes. The name SQLCODE isn't well,
better is SQLSTATE. It's very similar DB2 variable. I am not sure, so my
solutions is best. Propably not. It's only particular solution for
plpgsql. The files plpgsql.sql and plpgsql.out are diffs for regression
tests.
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
'0' for SQLSTATE and 'Sucessful completion' for SQLERRM.
Regards
Pavel Stehule
*** test.old/plpgsql.sql2005-02-22 08:18:27.0 +0100
--- test/plpgsql.sql2005-03-08 09:58:23.419281208 +0100
***
*** 1917,1920
create function void_return_expr() returns void as $$
begin
return 5;
! end;$$ language plpgsql;
--- 1917,1944
create function void_return_expr() returns void as $$
begin
return 5;
! end;$$ language plpgsql;
!
! --
! -- Test of built variables SQLERRM and SQLSTATE
! --
!
! 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();
!
!
*** test.old/plpgsql.out2005-02-22 08:18:25.0 +0100
--- test/plpgsql.out2005-03-08 09:56:58.272225528 +0100
***
*** 2242,2244
--- 2242,2252
ERROR: function returning void cannot specify RETURN expression at or near
"5" at character 72
LINE 3: return 5;
^
+ CREATE FUNCTION
+ psql:plpgsql.sql:1942: NOTICE: P0001 first exception
+ psql:plpgsql.sql:1942: NOTICE: 00 Sucessful completion
+ psql:plpgsql.sql:1942: NOTICE: P0001 last exception
+ trap_exceptions
+ -
+
+ (1 row)
diff -c -r src.old/gram.y src/gram.y
*** src.old/gram.y 2005-02-22 08:18:24.0 +0100
--- src/gram.y 2005-03-08 09:22:20.886036232 +0100
***
*** 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 */
***
*** 95,101
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
!
%type decl_sect
%type decl_varname
%typedecl_renname
--- 100,106
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
! %type fict_vars_sect
%type decl_sect
%type decl_varname
%typedecl_renname
***
*** 244,268
| ';'
;
! 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();
$$ = (PLpgSQL_stmt *)new;
}
;
decl_sect : opt_label
--- 249,288
| ';'
;
! pl_block : decl_sect fict_vars_sect K_BEGIN lno proc_sect
exception_sect K_END
{
PLpgSQL_stmt_block *new;
Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables for
Neil Conway <[EMAIL PROTECTED]> writes: > - Is there a reason why you've made the type of SQLCODE `text', rather > than integer? The value isn't an integer ... which gets back to my point that this is not compatible with Oracle's idea of SQLCODE and therefore we should *not* use that name for it. BTW: the patch has some memory-leak problems, I believe, because it is studiously not following the var->freeval protocol. Now that I look, it appears to be copied-and-pasted from some existing code that also gets this wrong :-( regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables for
Hello,
I used different format now. Documentation:
This patch is implementation of variables SQLERRM and SQLCODE for plpgsql
language. Variable SQLCODE 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
'0' for SQLCODE and 'Sucessful completion' for SQLERRM. Some example
of using is in file test.sql.
Regards
Pavel Stehule
--
-- Test of built variables SQLERRM and SQLCODE
--
create or replace function trap_exceptions() returns void as $_$
begin
begin
raise exception 'first exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
raise notice '% %', SQLCODE, SQLERRM;
begin
raise exception 'last exception';
exception when others then
raise notice '% %', SQLCODE, SQLERRM;
end;
return;
end; $_$ language plpgsql;
select trap_exceptions();
drop function trap_exceptions();
CREATE FUNCTION
NOTICE: P0001 first exception
NOTICE: 00 Sucessfull completation
NOTICE: P0001 last exception
trap_exceptions
-
(1 row)
DROP FUNCTION
diff -c -r src.old/gram.y src/gram.y
*** src.old/gram.y 2005-02-22 08:18:24.0 +0100
--- src/gram.y 2005-03-07 10:05:29.286336064 +0100
***
*** 80,85
--- 80,90
int n_initvars;
int *initvarnos;
} declhdr;
+ struct
+ {
+ int sqlcode_varno;
+ int sqlerrm_varno;
+ } fict_vars;
List*list;
PLpgSQL_type*dtype;
PLpgSQL_datum *scalar;/* a VAR,
RECFIELD, or TRIGARG */
***
*** 95,101
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
!
%type decl_sect
%type decl_varname
%typedecl_renname
--- 100,106
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
! %type fict_vars_sect
%type decl_sect
%type decl_varname
%typedecl_renname
***
*** 244,268
| ';'
;
! 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();
$$ = (PLpgSQL_stmt *)new;
}
;
decl_sect : opt_label
--- 249,288
| ';'
;
! 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->sqlcode_varno =
$2.sqlcode_varno;
! new->sqlerrm_varno =
$2.sqlerrm_varno;
plpgsql_ns_pop();
$$ = (PLpgSQL_stmt *)new;
}
;
+ fict_vars_s
Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables for
> > I think we discussed this last year and decided that it would be a bad > idea to use those names because Oracle's use of them is not exactly > compatible with our error codes and messages. SQLCODE in particular is > not compatible at all --- it's an integer in Oracle, isn't it? There is more incompatibilities to Oracle. SQLERRM is function on Oracle, only if you use it without parametr, returns current message error. SQLCODE is really integer. But it's only names. There is no problem change it. > > IIRC we had put off solving this problem until we decided what to do > with RAISE. There really needs to be some changes in RAISE to allow it > to raise a specific error code rather than always P0001, but exactly > what is still undecided. I didn't know it. But for my work is SQLERRM more important. I have more constraints on tables and I need detect which which constraints raise exception. The possibility EXCEPTION WITH OTHERS is nice, but not too much usefull because I have not possibility get some informations about except. > > Some other problems with your patch: no documentation, and not in > diff -c format. Plain diff patches are never acceptable because > it's too risky to apply them against files that might have changed > since you started working with them. Also, it's much easier to > deal with one patch than with a separate diff for each file. > (diff -c -r between an original and a modified directory is one > good way to produce a useful patch.) > I am not sure, I able create documentation - my english is poor. I will change diff's format and send patch again. Thank you Pavel ---(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
Re: [PATCHES] [HACKERS] Implementation of SQLCODE and SQLERRM variables for PL/pgSQL
Pavel Stehule <[EMAIL PROTECTED]> writes: > This is my second patch, than please will be tolerant :-). For one my > project I miss information about exception when I use EXCEPTION WITH > OTHERS THEN. I found two Oracle's varaibles SQLCODE and SQLERRM which > carry this information. I think we discussed this last year and decided that it would be a bad idea to use those names because Oracle's use of them is not exactly compatible with our error codes and messages. SQLCODE in particular is not compatible at all --- it's an integer in Oracle, isn't it? IIRC we had put off solving this problem until we decided what to do with RAISE. There really needs to be some changes in RAISE to allow it to raise a specific error code rather than always P0001, but exactly what is still undecided. Some other problems with your patch: no documentation, and not in diff -c format. Plain diff patches are never acceptable because it's too risky to apply them against files that might have changed since you started working with them. Also, it's much easier to deal with one patch than with a separate diff for each file. (diff -c -r between an original and a modified directory is one good way to produce a useful patch.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
