>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.
So, you have an input parameter named "session_id" and a query with a column named "session_id" - this is the problem.
The function never even gets to execute the "RETURN" statement - the exception occurred first - so whatever you were doing there was pointless.
On a side note It seems you missed the memo about the "char" type being largely deprecated...and furthermore if I rename the function signature "session_id" to "i_session_id" and replace the corresponding value in the SELECT statement I now get "operator does not exist: bigint = character. So you've setup an input type that differs from your column type.
So, yes, it is user error and while it was not due to the view that was all the information you provided at the time.
I'm not in the mood to fix these two items (name and type) and find the next oversight. I do suggest that, especially if you do not use "IN/OUT" arguments, you prefix your function argument names with something so that you eliminate the chance that a function variable and a query variable name collide. The main give-away here was the where clause expression "WHERE session_id = session_id" - how would you expect PostgreSQL to know which one is from the table and which one is from the function? The only other option is to pick one of them but in that case you'd simply get a constant TRUE and every row would be returned.
