Thread: Using row_to_json with %ROWTYPE ?
Hi, I have a function that broadly looks like this : create function doStuff() returns json as $$ DECLARE v_row my_view%ROWTYPE; BEGIN select * into strict v_row from my_view where foo=bar; select row_to_json(v_row) from v_row; END; $$ LANGUAGE plpgsql; However this does not seem to work ? What am I doing wrong ? Thanks Tim
On 02/05/2015 01:38 PM, Tim Smith wrote: > Hi, > > I have a function that broadly looks like this : > > create function doStuff() returns json as $$ > DECLARE > v_row my_view%ROWTYPE; > BEGIN > select * into strict v_row from my_view where foo=bar; > select row_to_json(v_row) from v_row; > END; > $$ LANGUAGE plpgsql; > > > However this does not seem to work ? What am I doing wrong ? Well for starters is: select * into strict v_row from my_view where foo=bar; returning more than one row? v_row can only hold one row at a time. Given that then: select row_to_json(v_row) from v_row; should be: select row_to_json(v_row); I would suggest taking a look at: http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING Also error messages would be helpful:) > > Thanks > > Tim > > -- Adrian Klaver adrian.klaver@aklaver.com
Tim Smith-2 wrote > Hi, > > I have a function that broadly looks like this : > > create function doStuff() returns json as $$ > DECLARE > v_row my_view%ROWTYPE; > BEGIN > select * into strict v_row from my_view where foo=bar; > select row_to_json(v_row) from v_row; > END; > $$ LANGUAGE plpgsql; > > However this does not seem to work ? What am I doing wrong ? I suspect that the main issue you are encountering is that "FROM v_row" is complaining that v_row is not a known relation. You really need to provide error messages or your observations in situations like this. A blanket "does not seem to work" is not enough when asking others to identify what you are doing wrong. A self-contained example is even better. If the above is true then this has nothing with row_to_json other than that is the function you choose to try and use. A simple "SELECT * FROM v_row" would get you the same error. David J. -- View this message in context: http://postgresql.nabble.com/Using-row-to-json-with-ROWTYPE-tp5836841p5836848.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> returning more than one row? v_row can only hold one row at a time. Absolutley not. (a) My where clause is a primary key (b) I have checked it manually, it only returns one row >You really need to provide error messages Yes, well PostgreSQL is being incredibly unhelpful in that respect, it says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is ambiguous)" ... but that is an utter lie. There is only one column called session_id in my view (in both the view output and the underlying view query, there is only one reference to "session_id") On 5 February 2015 at 21:57, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 02/05/2015 01:38 PM, Tim Smith wrote: >> >> Hi, >> >> I have a function that broadly looks like this : >> >> create function doStuff() returns json as $$ >> DECLARE >> v_row my_view%ROWTYPE; >> BEGIN >> select * into strict v_row from my_view where foo=bar; >> select row_to_json(v_row) from v_row; >> END; >> $$ LANGUAGE plpgsql; >> >> >> However this does not seem to work ? What am I doing wrong ? > > > Well for starters is: > > select * into strict v_row from my_view where foo=bar; > > returning more than one row? v_row can only hold one row at a time. > > Given that then: > > select row_to_json(v_row) from v_row; > > should be: > > select row_to_json(v_row); > > I would suggest taking a look at: > > http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING > > > Also error messages would be helpful:) > > >> >> Thanks >> >> Tim >> >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
On 02/05/2015 03:01 PM, Tim Smith wrote: > > returning more than one row? v_row can only hold one row at a time. > > Absolutley not. (a) My where clause is a primary key (b) I have > checked it manually, it only returns one row Well since there was no error message provided and my psychic hat is in the shop I had to start somewhere. > >> You really need to provide error messages > > Yes, well PostgreSQL is being incredibly unhelpful in that respect, it > says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is > ambiguous)" ... but that is an utter lie. There is only one column > called session_id in my view (in both the view output and the > underlying view query, there is only one reference to "session_id") Actually I would say this is a pretty big clue that: select row_to_json(v_row) from v_row; is causing a problem. Try commenting it out and see what happens? > > On 5 February 2015 at 21:57, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 02/05/2015 01:38 PM, Tim Smith wrote: >>> >>> Hi, >>> >>> I have a function that broadly looks like this : >>> >>> create function doStuff() returns json as $$ >>> DECLARE >>> v_row my_view%ROWTYPE; >>> BEGIN >>> select * into strict v_row from my_view where foo=bar; >>> select row_to_json(v_row) from v_row; >>> END; >>> $$ LANGUAGE plpgsql; >>> >>> >>> However this does not seem to work ? What am I doing wrong ? >> >> >> Well for starters is: >> >> select * into strict v_row from my_view where foo=bar; >> >> returning more than one row? v_row can only hold one row at a time. >> >> Given that then: >> >> select row_to_json(v_row) from v_row; >> >> should be: >> >> select row_to_json(v_row); >> >> I would suggest taking a look at: >> >> http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING >> >> >> Also error messages would be helpful:) >> >> >>> >>> Thanks >>> >>> Tim >>> >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com -- Adrian Klaver adrian.klaver@aklaver.com
> returning more than one row? v_row can only hold one row at a time.
Absolutley not. (a) My where clause is a primary key (b) I have
checked it manually, it only returns one row
>You really need to provide error messages
Yes, well PostgreSQL is being incredibly unhelpful in that respect, it
says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is
ambiguous)" ... but that is an utter lie. There is only one column
called session_id in my view (in both the view output and the
underlying view query, there is only one reference to "session_id")
PostgreSQL doesn't lie - it just doesn't always give all of the information you need
to understand what it is seeing.
You have a view definition problem since nowhere in the code you provide should
session_id be resolved.
A simple:
SELECT * FROM my_view;
would prove out that theory.
If that works then most probably the my_view view that the function sees is different
than the one that you think it is seeing.
On 5 February 2015 at 21:57, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 02/05/2015 01:38 PM, Tim Smith wrote:
>>
>> Hi,
>>
>> I have a function that broadly looks like this :
>>
>> create function doStuff() returns json as $$
>> DECLARE
>> v_row my_view%ROWTYPE;
>> BEGIN
>> select * into strict v_row from my_view where foo=bar;
>> select row_to_json(v_row) from v_row;
A third problem you will hit, when you fix the syntax, is that the
SELECT row_to_json(...) command has no target and thus needs
to use PERFORM, not SELECT.
David J.
> PostgreSQL doesn't lie
Well if its not lying its one big stinking bug !
How about you tell me where you see these duplicate columns in my view
that PostgreSQL is apparently not lying to me about ....
View "public.app_val_session_vw"
Column | Type | Modifiers
-------------------+---------------+-----------
session_id | bigint |
session_ip | inet |
session_user_agent | character(40) |
session_start | bigint |
session_lastactive | bigint |
user_id | bigint |
tenant_id | bigint |
reseller_id | bigint |
tenant_name | text |
user_fname | text |
user_lname | text |
user_email | text |
user_phone | bigint |
user_seed | character(16) |
user_passwd | character(60) |
user_lastupdate | bigint |
tenant_lastupdate | bigint |
On 5 February 2015 at 23:19, David Johnston <david.g.johnston@gmail.com> wrote:
> On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith <randomdev4+postgres@gmail.com>
> wrote:
>>
>> > returning more than one row? v_row can only hold one row at a time.
>>
>> Absolutley not. (a) My where clause is a primary key (b) I have
>> checked it manually, it only returns one row
>>
>> >You really need to provide error messages
>>
>> Yes, well PostgreSQL is being incredibly unhelpful in that respect, it
>> says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is
>> ambiguous)" ... but that is an utter lie. There is only one column
>> called session_id in my view (in both the view output and the
>> underlying view query, there is only one reference to "session_id")
>>
> PostgreSQL doesn't lie - it just doesn't always give all of the information
> you need
> to understand what it is seeing.
>
> You have a view definition problem since nowhere in the code you provide
> should
> session_id be resolved.
>
> A simple:
>
> SELECT * FROM my_view;
>
> would prove out that theory.
>
> If that works then most probably the my_view view that the function sees is
> different
> than the one that you think it is seeing.
>
>>
>> On 5 February 2015 at 21:57, Adrian Klaver <adrian.klaver@aklaver.com>
>> wrote:
>> > On 02/05/2015 01:38 PM, Tim Smith wrote:
>> >>
>> >> Hi,
>> >>
>> >> I have a function that broadly looks like this :
>> >>
>> >> create function doStuff() returns json as $$
>> >> DECLARE
>> >> v_row my_view%ROWTYPE;
>> >> BEGIN
>> >> select * into strict v_row from my_view where foo=bar;
>> >> select row_to_json(v_row) from v_row;
>>
>
> A third problem you will hit, when you fix the syntax, is that the
> SELECT row_to_json(...) command has no target and thus needs
> to use PERFORM, not SELECT.
> David J.
>
You're most welcome to look at my view definition view if you don't
believe me ....
View definition:
SELECT a.session_id,
a.session_ip,
a.session_user_agent,
a.session_start,
a.session_lastactive,
b.user_id,
b.tenant_id,
b.reseller_id,
b.tenant_name,
b.user_fname,
b.user_lname,
b.user_email,
b.user_phone,
b.user_seed,
b.user_passwd,
b.user_lastupdate,
b.tenant_lastupdate
FROM app_sessions a,
app_users_vw b
WHERE a.user_id = b.user_id;
You're most welcome to look at my view definition view if you don't
believe me ....
View definition:
SELECT a.session_id,
a.session_ip,
a.session_user_agent,
a.session_start,
a.session_lastactive,
b.user_id,
b.tenant_id,
b.reseller_id,
b.tenant_name,
b.user_fname,
b.user_lname,
b.user_email,
b.user_phone,
b.user_seed,
b.user_passwd,
b.user_lastupdate,
b.tenant_lastupdate
FROM app_sessions a,
app_users_vw b
WHERE a.user_id = b.user_id;
So that view and definition are correct.
So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult to predict.
I guess it is possible that:
(SELECT v_row FROM v_row) would give that message but I get a "relation v_row does not exist" error when trying to replicate the scenario.
It may even be a bug but since you have not provided a self-contained test case, nor the version of PostgreSQL, the assumption is user error.
David J.
> So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult topredict. Seriously ? You still want to continue calling it user-error ? There is no other view, there is no other schema , I am not hiding anything from you ! On 5 February 2015 at 23:38, David Johnston <david.g.johnston@gmail.com> wrote: > On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith <randomdev4+postgres@gmail.com> > wrote: >> >> You're most welcome to look at my view definition view if you don't >> believe me .... >> >> View definition: >> SELECT a.session_id, >> a.session_ip, >> a.session_user_agent, >> a.session_start, >> a.session_lastactive, >> b.user_id, >> b.tenant_id, >> b.reseller_id, >> b.tenant_name, >> b.user_fname, >> b.user_lname, >> b.user_email, >> b.user_phone, >> b.user_seed, >> b.user_passwd, >> b.user_lastupdate, >> b.tenant_lastupdate >> FROM app_sessions a, >> app_users_vw b >> WHERE a.user_id = b.user_id; > > > So that view and definition are correct. > > So either PostgreSQL is seeing a different view (in a different schema) or > the function is confused in ways difficult to predict. > > I guess it is possible that: > > (SELECT v_row FROM v_row) would give that message but I get a "relation > v_row does not exist" error when trying to replicate the scenario. > > It may even be a bug but since you have not provided a self-contained test > case, nor the version of PostgreSQL, the assumption is user error. > > David J. >
app_sessions is a table and app_users_vw is not hiding anything from you : tenant_id tenant_name tenant_shortname reseller_id user_id user_failedlogins user_fname user_lname user_email user_phone user_passwd user_seed user_hidden user_candelete user_newseed user_lastupdate tenant_lastupdate On 5 February 2015 at 23:38, David Johnston <david.g.johnston@gmail.com> wrote: > On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith <randomdev4+postgres@gmail.com> > wrote: >> >> You're most welcome to look at my view definition view if you don't >> believe me .... >> >> View definition: >> SELECT a.session_id, >> a.session_ip, >> a.session_user_agent, >> a.session_start, >> a.session_lastactive, >> b.user_id, >> b.tenant_id, >> b.reseller_id, >> b.tenant_name, >> b.user_fname, >> b.user_lname, >> b.user_email, >> b.user_phone, >> b.user_seed, >> b.user_passwd, >> b.user_lastupdate, >> b.tenant_lastupdate >> FROM app_sessions a, >> app_users_vw b >> WHERE a.user_id = b.user_id; > > > So that view and definition are correct. > > So either PostgreSQL is seeing a different view (in a different schema) or > the function is confused in ways difficult to predict. > > I guess it is possible that: > > (SELECT v_row FROM v_row) would give that message but I get a "relation > v_row does not exist" error when trying to replicate the scenario. > > It may even be a bug but since you have not provided a self-contained test > case, nor the version of PostgreSQL, the assumption is user error. > > David J. >
Tim Smith <randomdev4+postgres@gmail.com> writes:
> Yes, well PostgreSQL is being incredibly unhelpful in that respect, it
> says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is
> ambiguous)" ... but that is an utter lie. There is only one column
> called session_id in my view (in both the view output and the
> underlying view query, there is only one reference to "session_id")
A reasonably likely bet is that the ambiguity is between a column name
exposed by the query and some variable of the same name declared within
the plpgsql function. But, as has been mentioned repeatedly, you've not
shown us enough detail to permit a positive diagnosis.
regards, tom lane
On 02/05/2015 03:25 PM, Tim Smith wrote: >> PostgreSQL doesn't lie > > Well if its not lying its one big stinking bug ! In my experience Postgres does not randomly make up error messages. Somewhere it is seeing a duplicate column. > > How about you tell me where you see these duplicate columns in my view > that PostgreSQL is apparently not lying to me about .... > So then this is not the problem, which moves the troubleshooting to the function. Have you tried the previous suggestions on modifying the function? -- Adrian Klaver adrian.klaver@aklaver.com
On 02/05/2015 03:41 PM, Tim Smith wrote: >> So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficultto predict. > > Seriously ? You still want to continue calling it user-error ? There > is no other view, there is no other schema , I am not hiding anything > from you ! > No, what we are trying to do is work the solution, not the problem. The problem being you are getting a error, the solution being tracking down where the error is coming from. If you start a problem report with little or no information, you have to expect people are going to have to ask a spectrum of questions to get at the information necessary to solve the problem. -- Adrian Klaver adrian.klaver@aklaver.com
Alright then, here you go ... Postgres 9.4
We start with a clean database :
json_return_debugdb=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
json_return_debugdb=> \dt
No relations found.
json_return_debugdb=> \dv
No relations found.
We replicate a basic version of app_val_session_vw:
create table app_sessions (session_id bigint primary key,user_id
bigint unique not null, session_ip inet);
create table app_users (user_id bigint primary key,user_name text,
user_active boolean not null);
create view app_users_vw as select * from app_users where user_active=true;
create view app_val_session_vw as select
a.session_id,a.session_ip,b.user_name,b.user_id from app_sessions a,
app_users b where a.user_id=b.user_id;
We insert data :
insert into app_users values(1,’Foobar',true);
insert into app_sessions(441122,1,’10.11.12.13’,);
json_return_debugdb=> select
validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: Failed to validate session for session 441122 (SQLSTATE: 42702
- SQLERRM: column reference "session_id" is ambiguous)
HINT: Database error occured (sval fail)
On 5 February 2015 at 23:58, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 02/05/2015 03:25 PM, Tim Smith wrote:
>>>
>>> PostgreSQL doesn't lie
>>
>>
>> Well if its not lying its one big stinking bug !
>
>
> In my experience Postgres does not randomly make up error messages.
> Somewhere it is seeing a duplicate column.
>
>>
>> How about you tell me where you see these duplicate columns in my view
>> that PostgreSQL is apparently not lying to me about ....
>>
>
> So then this is not the problem, which moves the troubleshooting to the
> function.
>
> Have you tried the previous suggestions on modifying the function?
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
And if you want my exact version of Postgres its "PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit" (taken from the Postgres APT repository)
>Von: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]" im Auftrag von "David Johnston [david.g.johnston@gmail.com]
>Gesendet: Freitag, 6. Februar 2015 00:38
>An: Tim Smith
>Cc: Adrian Klaver; pgsql-general
>Betreff: Re: [GENERAL] Using row_to_json with %ROWTYPE ?
>On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith <randomdev4+postgres@gmail.com> wrote:
>
> You're most welcome to look at my view definition view if you don't
> believe me ....
>
> View definition:
> SELECT a.session_id,
> a.session_ip,
> a.session_user_agent,
> a.session_start,
> a.session_lastactive,
> b.user_id,
> b.tenant_id,
> b.reseller_id,
> b.tenant_name,
> b.user_fname,
> b.user_lname,
> b.user_email,
> b.user_phone,
> b.user_seed,
> b.user_passwd,
> b.user_lastupdate,
> b.tenant_lastupdate
> FROM app_sessions a,
> app_users_vw b
> WHERE a.user_id = b.user_id;
>
>?So that view and definition are correct.
>So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult to predict.
>I guess it is possible that:
>(SELECT v_?row FROM v_row) would give that message but I get a "relation v_row does not exist" error when trying to replicate the scenario.
>?It may even be a bug but since you have not provided a self-contained test case, nor the version of PostgreSQL, the assumption is user error.?
>David J.
Hello,
I don't know if there is some internal confusion when using the ROWTYPE (bug?)
but if this helps, following function is equivalent and does the job:
create or replace function doStuff() returns json as $$
select row_to_json(app_val_session_vw) from app_val_session_vw WHERE ...;
$$ LANGUAGE sql;
Nice work-around Marc. Thank you ! On 6 February 2015 at 13:01, Marc Mamin <M.Mamin@intershop.de> wrote: > >>Von: pgsql-general-owner@postgresql.org >> [pgsql-general-owner@postgresql.org]" im Auftrag von "David Johnston >> [david.g.johnston@gmail.com] >>Gesendet: Freitag, 6. Februar 2015 00:38 >>An: Tim Smith >>Cc: Adrian Klaver; pgsql-general >>Betreff: Re: [GENERAL] Using row_to_json with %ROWTYPE ? >>On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith <randomdev4+postgres@gmail.com> >> wrote: >> >> You're most welcome to look at my view definition view if you don't >> believe me .... >> >> View definition: >> SELECT a.session_id, >> a.session_ip, >> a.session_user_agent, >> a.session_start, >> a.session_lastactive, >> b.user_id, >> b.tenant_id, >> b.reseller_id, >> b.tenant_name, >> b.user_fname, >> b.user_lname, >> b.user_email, >> b.user_phone, >> b.user_seed, >> b.user_passwd, >> b.user_lastupdate, >> b.tenant_lastupdate >> FROM app_sessions a, >> app_users_vw b >> WHERE a.user_id = b.user_id; >> >>?So that view and definition are correct. >>So either PostgreSQL is seeing a different view (in a different schema) or >> the function is confused in ways difficult to predict. >>I guess it is possible that: >>(SELECT v_?row FROM v_row) would give that message but I get a "relation >> v_row does not exist" error when trying to replicate the scenario. >>?It may even be a bug but since you have not provided a self-contained test >> case, nor the version of PostgreSQL, the assumption is user error.? >>David J. > > Hello, > I don't know if there is some internal confusion when using the ROWTYPE > (bug?) > but if this helps, following function is equivalent and does the job: > > create or replace function doStuff() returns json as $$ > > select row_to_json(app_val_session_vw) from app_val_session_vw WHERE ...; > > $$ LANGUAGE sql;
On 02/06/2015 04:19 AM, Tim Smith wrote:
> Alright then, here you go ... Postgres 9.4
>
> We start with a clean database :
>
> json_return_debugdb=> \dn
> List of schemas
> Name | Owner
> --------+----------
> public | postgres
> (1 row)
>
> json_return_debugdb=> \dt
> No relations found.
> json_return_debugdb=> \dv
> No relations found.
>
>
>
> We replicate a basic version of app_val_session_vw:
>
> create table app_sessions (session_id bigint primary key,user_id
> bigint unique not null, session_ip inet);
> create table app_users (user_id bigint primary key,user_name text,
> user_active boolean not null);
> create view app_users_vw as select * from app_users where user_active=true;
> create view app_val_session_vw as select
> a.session_id,a.session_ip,b.user_name,b.user_id from app_sessions a,
> app_users b where a.user_id=b.user_id;
>
> We insert data :
> insert into app_users values(1,’Foobar',true);
> insert into app_sessions(441122,1,’10.11.12.13’,);
>
>
>
> json_return_debugdb=> select
> validateSession('441122','10.11.12.13','abc',3600,3600);
> ERROR: Failed to validate session for session 441122 (SQLSTATE: 42702
> - SQLERRM: column reference "session_id" is ambiguous)
> HINT: Database error occured (sval fail)
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.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 02/06/2015 05:33 AM, Tim Smith wrote:
> Nice work-around Marc. Thank you !
>
Nice that it works, but in the end it proves that the issue is not with
row_to_json and a row type, but with how %ROW_TYPE is being used in a
specific function. To prove it, using your earlier function modified for
your latest test case:
CREATE OR REPLACE FUNCTION public.dostuff()
RETURNS json
LANGUAGE plpgsql
AS $function$
DECLARE
v_row app_val_session_vw %ROWTYPE;
j_return json;
BEGIN
select * into strict v_row from app_val_session_vw where user_id=1;
select into j_return row_to_json(v_row);
RETURN j_return;
END;
$function$
test=# select dostuff();
dostuff
-----------------------------------------------------------------------------------
{"session_id":441122,"session_ip":"10.11.12.13","user_name":"Foobar","user_id":1}
(1 row)
--
Adrian Klaver
adrian.klaver@aklaver.com
>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.
>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.
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
CREATE FUNCTION validateSession(session_id char(64),client_ip
inet,user_agent char(40),forcedTimeout bigint,sessionTimeout bigint)
RETURNS json AS $$
CREATE OR REPLACE FUNCTION public.validatesession(s_id character, client_ip inet, user_agent character, forcedtimeout bigint, sessiontimeout bigint)
RETURNS json
As an aside, and going from memory, you will note that Adrian kept the "character" type in the function signature
but removed the length specifier. PostgreSQL does not store that information and so will not prevent a call from passing in a string longer than 64 characters into the function. This applies to any specification in () following a type declaration (say for numeric or timestamptz)
David J.
Re:So, you have an input parameter named "session_id" and a query with a column named "session_id" - this is the problem. Well, I'll re-try with a revised function, but surely the database could have come up with a more meaningful and insightful message than the coded incomprehensible error message it did ? I would say its not only user error, its developer error too for creating such confusing error messages !
Thank you Adrian. Will give this a go over the weekend.
On 6 February 2015 at 17:23, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> 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
On 02/06/2015 10:26 AM, Tim Smith wrote:
> Re:So, you have an input parameter named "session_id" and a query with
> a column named "session_id" - this is the problem.
>
> Well, I'll re-try with a revised function, but surely the database
> could have come up with a more meaningful and insightful message than
> the coded incomprehensible error message it did ? I would say its
> not only user error, its developer error too for creating such
> confusing error messages !
Well actually you did yourself a disservice by including the EXCEPT
code. That changed the error message. Taking that code out and running
the failing function you get:
test-> validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: column reference "session_id" is ambiguous
LINE 2: session_id=session_id and session_ip=client_ip
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: select * from app_val_session_vw where
session_id=session_id and session_ip=client_ip
CONTEXT: PL/pgSQL function
validatesession(character,inet,character,bigint,bigint) line 7 at SQL
statement
I would say that is fairly specific:)
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Friday, February 6, 2015, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 02/06/2015 10:26 AM, Tim Smith wrote:Re:So, you have an input parameter named "session_id" and a query with
a column named "session_id" - this is the problem.
Well, I'll re-try with a revised function, but surely the database
could have come up with a more meaningful and insightful message than
the coded incomprehensible error message it did ? I would say its
not only user error, its developer error too for creating such
confusing error messages !
Well actually you did yourself a disservice by including the EXCEPT code. That changed the error message. Taking that code out and running the failing function you get:
test-> validateSession('441122','10.11.12.13','abc',3600,3600);
ERROR: column reference "session_id" is ambiguous
LINE 2: session_id=session_id and session_ip=client_ip
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: select * from app_val_session_vw where
session_id=session_id and session_ip=client_ip
CONTEXT: PL/pgsql function validatesession(character,inet,character,bigint,bigint) line 7 at SQL statement
I would say that is fairly specific:)
The exception block is ok, you want to report the session-id passed (via raise notice or similar), but you want to use the "RAISE;" form (i.e., no args) to re-raise the original error.
David J.