From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org>, david(dot)g(dot)johnston(at)gmail(dot)com |
Subject: | Re: Using row_to_json with %ROWTYPE ? |
Date: | 2015-02-05 23:07:35 |
Message-ID: | 54D3F7B7.8000407@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2015-02-05 23:19:41 | Re: Using row_to_json with %ROWTYPE ? |
Previous Message | Tim Smith | 2015-02-05 23:01:03 | Re: Using row_to_json with %ROWTYPE ? |