Re: Using row_to_json with %ROWTYPE ?

On Fri, Feb 6, 2015 at 9:55 AM, Tim Smith <randomdev4+postgres(at)gmail(dot)com>
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.
>

​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.