On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith <randomdev4+postg...@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.kla...@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.
