>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.
David J.
