Re: Using row_to_json with %ROWTYPE ?

> 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(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