Re: Using row_to_json with %ROWTYPE ? - Mailing list pgsql-general

> PostgreSQL doesn't lie

Well if its not lying its one big stinking bug !

How about you tell me where you see these duplicate columns in my view
that PostgreSQL is apparently not lying to me about  ....

       View "public.app_val_session_vw"
      Column       |     Type      | Modifiers
-------------------+---------------+-----------
session_id         | bigint |
session_ip         | inet          |
session_user_agent | character(40) |
session_start      | bigint        |
session_lastactive | bigint        |
user_id            | bigint |
tenant_id          | bigint |
reseller_id        | bigint |
tenant_name        | text          |
user_fname         | text          |
user_lname         | text          |
user_email         | text          |
user_phone         | bigint        |
user_seed          | character(16) |
user_passwd        | character(60) |
user_lastupdate    | bigint        |
tenant_lastupdate  | bigint        |

On 5 February 2015 at 23:19, David Johnston <david.g.johnston@gmail.com> wrote:
> On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith <randomdev4+postgres@gmail.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.klaver@aklaver.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.
>