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@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;
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@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
