From: | David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Tim Smith <randomdev4+postgres(at)gmail(dot)com> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Using row_to_json with %ROWTYPE ? |
Date: | 2015-02-05 23:19:41 |
Message-ID: | CAKFQuwbmpqgY7B7S+XsChxa7B2zKocf2JFtuB2w=ZQ1PwaJFUQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith <randomdev4+postgres(at)gmail(dot)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(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;
>
>
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Smith | 2015-02-05 23:25:20 | Re: Using row_to_json with %ROWTYPE ? |
Previous Message | Adrian Klaver | 2015-02-05 23:07:35 | Re: Using row_to_json with %ROWTYPE ? |