Re: Using row_to_json with %ROWTYPE ?

Thank you Adrian. Will give this a go over the weekend.

On 6 February 2015 at 17:23, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> On 02/06/2015 08:55 AM, Tim Smith wrote:
>>>
>>> Unfortunately the function definition is not given and that is where you
>>> are seeing the error.
>>> To figure this out we will need to see the function.
>>
>>
>> Geez, there's just no satisfying some people ! ;-)
>>
>> I did actually show you my function in an earlier mail .... but my
>> current bodged minimised version looks like this :
>>
>>
>> CREATE FUNCTION validateSession(session_id char(64),client_ip
>> inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint)
>> RETURNS json AS $$
>> DECLARE
>> v_now bigint;
>> v_row app_val_session_vw%ROWTYPE;
>> BEGIN
>> v_now := extract(epoch FROM now())::bigint;
>> select * into strict v_row from app_val_session_vw where
>> session_id=session_id and session_ip=client_ip;
>> RETURN row_to_json(v_row);
>> EXCEPTION
>> WHEN OTHERS THEN
>> RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE: %
>> - SQLERRM: %)', session_id,SQLSTATE,SQLERRM
>> USING HINT = 'Database error occured (sval fail)';
>> END;
>> $$ LANGUAGE plpgsql;
>>
>>
>> Note that I have tried a million and one different versions of the
>> line "RETURN row_to_json(v_row);" .... including declaring a JSON type
>> var and putting hte result into that before returning. But nothing
>> works, it always comes back with the same session_id nonsense.
>>
>>
>
> Changed to work:
>
> CREATE OR REPLACE FUNCTION public.validatesession(s_id character, client_ip
> inet, user_agent character, forcedtimeout bigint, sessiontimeout bigint)
> RETURNS json
> LANGUAGE plpgsql
> AS $function$
> DECLARE
> v_now bigint;
> v_row app_val_session_vw %ROWTYPE;
> BEGIN
> v_now := extract(epoch FROM now())::bigint;
> select * into strict v_row from app_val_session_vw AS vw where
> vw.session_id=s_id::int and session_ip=client_ip;
> RETURN row_to_json(v_row);
> EXCEPTION
> WHEN OTHERS THEN
> RAISE EXCEPTION 'Failed to validate session for session % (SQLSTATE:
> %
> - SQLERRM: %)', v_row.session_id,SQLSTATE,SQLERRM
> USING HINT = 'Database error occured (sval fail)';
> END;
> $function$
>
> test=# select
> validateSession('441122','10.11.12.13','abc',3600,3600);
> validatesession
> -----------------------------------------------------------------------------------
>
> {"session_id":441122,"session_ip":"10.11.12.13","user_name":"Foobar","user_id":1}
> (1 row)
>
>
> The problem was a conflict between the session_id argument/variable passed
> in and the session_id field in app_val_session_vw.
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com