Thread: BUG #18443: jsonb_agg issue. Again.

BUG #18443: jsonb_agg issue. Again.

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      18443
Logged by:          Alexander Tsaregorodtsev
Email address:      pfunk@mail.ru
PostgreSQL version: 12.18
Operating system:   Official docker container
Description:

Greetings!
Is it expected behavior?


--simple function raising ONE notice and returning record of 2 int`s
drop function if exists public.out2(jsonb);
create or replace function public.out2(_in jsonb, a out int, b out int)
returns record
as
$body$
begin
        raise notice '***';
end;
$body$
  language plpgsql;
  
--similar function raising notice but returning record of 3 int`s  
drop function if exists public.out3();
create or replace function public.out3(_in jsonb, a out int, b out int, c
out int)
returns record
as
$body$
begin
        raise notice '***';
end;
$body$
  language plpgsql;

--view with data
drop view if exists tmp_view;
create or replace temp view tmp_view(col) as 
values
(1::bigint), (2), (3), (4);

db=> select * from tmp_view;
 col
-----
   1
   2
   3
   4
(4 rows)



--result is 1 aggregated string with jsonb array
select jsonb_agg(jsonb_build_object('col', col)) from tmp_view;

db=> select jsonb_agg(jsonb_build_object('col', col)) from tmp_view;
                    jsonb_agg
--------------------------------------------------
 [{"col": 1}, {"col": 2}, {"col": 3}, {"col": 4}]
(1 row)


--Problem! function out2 has invoked 2 times (two NOTICE`s and accidentally
number of output parameters)
select (public.out2(jsonb_agg(jsonb_build_object('col', col)))).* --,
jsonb_agg(jsonb_build_object('col', col))
from tmp_view;

db=> select (public.out2(jsonb_agg(jsonb_build_object('col', col)))).* --,
jsonb_agg(jsonb_build_object('col', col))
from tmp_view;
NOTICE:  ***
NOTICE:  ***
 a | b
---+---
   |
(1 row)


--Problem! function out3 has invoked 3 times (three NOTICE`s and
accidentally number of output parameters)
select (public.out3(jsonb_agg(jsonb_build_object('col', col)))).* --,
jsonb_agg(jsonb_build_object('col', col))
from tmp_view;
db=> select (public.out3(jsonb_agg(jsonb_build_object('col', col)))).* --,
jsonb_agg(jsonb_build_object('col', col))
from tmp_view;
NOTICE:  ***
NOTICE:  ***
NOTICE:  ***
 a | b | c
---+---+---
   |   |
(1 row)



Something similar was reported in
https://www.postgresql.org/message-id/flat/18365.1529018904%40sss.pgh.pa.us#a926c8cabd0cefb1f7ba50a387f5272a
Could it be related?


Re: BUG #18443: jsonb_agg issue. Again.

From
"David G. Johnston"
Date:
On Fri, Apr 19, 2024 at 8:25 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      18443
Logged by:          Alexander Tsaregorodtsev
Email address:      pfunk@mail.ru
PostgreSQL version: 12.18
Operating system:   Official docker container
Description:       

Greetings!
Is it expected behavior?


--simple function raising ONE notice and returning record of 2 int`s
drop function if exists public.out2(jsonb);
create or replace function public.out2(_in jsonb, a out int, b out int)
returns record
as
$body$
begin
        raise notice '***';
end;
$body$
  language plpgsql;

--similar function raising notice but returning record of 3 int`s 
drop function if exists public.out3();
create or replace function public.out3(_in jsonb, a out int, b out int, c
out int)
returns record

--Problem! function out2 has invoked 2 times (two NOTICE`s and accidentally
number of output parameters)
select (public.out2(jsonb_agg(jsonb_build_object('col', col)))).* --,
jsonb_agg(jsonb_build_object('col', col))
from tmp_view;


You wrote a table-producing function (one row but three columns).  Those need [1] to be executed in the FROM clause, via an implicit or explicit LATERAL join if you have data from other relations being fed in as input arguments.

David J.

1. well, at least if you don't want to see this kind of artifact where the way to expand .* is to copy the expression preceding it.

Re: BUG #18443: jsonb_agg issue. Again.

From
Ц
Date:
You wrote a table-producing function (one row but three columns).  Those need [1] to be executed in the FROM clause, via an implicit or explicit LATERAL join if you have data from other relations being fed in as input arguments.
 
David J.
 
1. well, at least if you don't want to see this kind of artifact where the way to expand .* is to copy the expression preceding it.
 
Do you mean select (out3).* .. equal to select (out3).col1, (out3).col3, (out3).col3 … ?
Didn`t even suppose that..
Sometimes I've used (row).col or (row).* in select list.
Would be appreciated if you could provide keywords for googling.
And yeah, after hours of confusion rewrote it to:
 
select o.*
from 
  (select jsonb_agg(t) as jsonbagg from tmp_view t) j
  , lateral public.out3(j.jsonbagg) o;
 
And it works just fine!
 
 

Re: BUG #18443: jsonb_agg issue. Again.

From
Ц
Date:
>> You wrote a table-producing function (one row but three columns).  Those need [1] to be executed in the FROM clause, via an implicit or explicit LATERAL join if you have data from other relations being fed in as input arguments.
>>
>> David J.
>>
>> 1. well, at least if you don't want to see this kind of artifact where the way to expand .* is to copy the expression preceding it.
>>
 
>Do you mean select (out3).* .. equal to select (out3).col1, (out3).col3, (out3).col3 … ?
>Didn`t even suppose that..
>Sometimes I've used (row).col or (row).* in select list.
>Would be appreciated if you could provide keywords for googling.
 
 
Of course semantically it is equal).Never mind. 
 
 
 
 

Re: BUG #18443: jsonb_agg issue. Again.

From
Tom Lane
Date:
=?UTF-8?B?0KY=?= <pfunk@mail.ru> writes:
>> You wrote a table-producing function (one row but three columns).  Those need [1] to be executed in the FROM clause,
viaan implicit or explicit LATERAL join if you have data from other relations being fed in as input arguments. 
>>
>> 1. well, at least if you don't want to see this kind of artifact where the way to expand .* is to copy the
expressionpreceding it. 

> Do you mean select (out3).* .. equal to select (out3).col1, (out3).col3, (out3).col3 … ?
> Didn`t even suppose that..
> Sometimes I've used (row).col or (row).* in select list.
> Would be appreciated if you could provide keywords for googling.

The main documentation about SELECT lists is

https://www.postgresql.org/docs/current/queries-select-lists.html

which points you to

https://www.postgresql.org/docs/current/rowtypes.html#ROWTYPES-USAGE

where there's a Tip explaining exactly this point.

            regards, tom lane