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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
