Re: Using row_to_json with %ROWTYPE ?

On Friday, February 6, 2015, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-errors-and-messages.html

David J.