> 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@xxxxxxxxxxx> 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.
