On Friday, February 6, 2015, Adrian Klaver <
adrian.klaver@aklaver.com> wrote:
On 02/06/2015 10:26 AM, Tim Smith wrote:
Re:So, you have an input parameter named "session_id" and a query with
a column named "session_id" - this is the problem.
Well, I'll re-try with a revised function, but surely the database
could have come up with a more meaningful and insightful message than
the coded incomprehensible error message it did ? I would say its
not only user error, its developer error too for creating such
confusing error messages !
Well actually you did yourself a disservice by including the EXCEPT code. That changed the error message. Taking that code out and running the failing function you get:
test-> validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: column reference "session_id" is ambiguous
LINE 2: session_id=session_id and session_ip=client_ip
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: select * from app_val_session_vw where
session_id=session_id and session_ip=client_ip
CONTEXT: PL/pgsql function validatesession(character,inet,character,bigint,bigint) line 7 at SQL statement
I would say that is fairly specific:)
The exception block is ok, you want to report the session-id passed (via raise notice or similar), but you want to use the "RAISE;" form (i.e., no args) to re-raise the original error.
David J.