Re: Using row_to_json with %ROWTYPE ?

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.klaver@aklaver.com