agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedselect unnest(), unnest()
3+ messages / 2 participants
[nested] [flat]
* select unnest(), unnest()
@ 2018-04-01 19:38 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jasmin Dizdarevic @ 2018-04-01 19:38 UTC (permalink / raw)
To: pgsql-sql
Hi, we have some queries that use unnest() function.
create table configuration (
id int,
advisor text,
branch text,
primary key(id)
);
insert into configuration values
(1,'a','00,01'),
(2,'a','00'),
(3,'b','02,03'),
(4,'c','05'),
(5,'d,e','00,01');
select id, unnest(string_to_array(advisor,',')), unnest
(string_to_array(branch,','))
from configuration;
PG9.6:
id | unnest | unnest
----+--------+--------
1 | a | 00
1 | a | 01
2 | a | 00
3 | b | 02
3 | b | 03
4 | c | 05
5 | d | 00
5 | e | 01
PG10:
id | unnest | unnest
----+--------+--------
1 | a | 00
1 | | 01
2 | a | 00
3 | b | 02
3 | | 03
4 | c | 05
5 | d | 00
5 | e | 01
- Is this wanted in PG10 or a bug?
- Is there a way to change the behaviour of pgsql to produce output like
9.6?
- A smooth sql-workaround?
Thank you very much!
Regards, Jasmin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select unnest(), unnest()
@ 2018-04-01 19:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2018-04-01 19:49 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> writes:
> select id, unnest(string_to_array(advisor,',')), unnest
> (string_to_array(branch,','))
> from configuration;
Yes, the behavior for cases like this changed in PG 10. Read the
release notes:
* Change the implementation of set-returning functions appearing in a
query's SELECT list (Andres Freund)
Set-returning functions are now evaluated before evaluation of scalar
expressions in the SELECT list, much as though they had been placed in
a LATERAL FROM-clause item. This allows saner semantics for cases
where multiple set-returning functions are present. If they return
different numbers of rows, the shorter results are extended to match
the longest result by adding nulls. Previously the results were cycled
until they all terminated at the same time, producing a number of rows
equal to the least common multiple of the functions' periods. In
addition, set-returning functions are now disallowed within CASE and
COALESCE constructs. For more information see Section 37.4.8.
> - Is there a way to change the behaviour of pgsql to produce output like
> 9.6?
> - A smooth sql-workaround?
LATERAL would probably help you; see the examples in 37.4.8.
https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select unnest(), unnest()
@ 2018-04-01 20:25 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jasmin Dizdarevic @ 2018-04-01 20:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thank you, Tom. Lateral works.
happy easter!
2018-04-01 21:49 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
> Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> writes:
> > select id, unnest(string_to_array(advisor,',')), unnest
> > (string_to_array(branch,','))
> > from configuration;
>
> Yes, the behavior for cases like this changed in PG 10. Read the
> release notes:
>
> * Change the implementation of set-returning functions appearing in a
> query's SELECT list (Andres Freund)
>
> Set-returning functions are now evaluated before evaluation of scalar
> expressions in the SELECT list, much as though they had been placed in
> a LATERAL FROM-clause item. This allows saner semantics for cases
> where multiple set-returning functions are present. If they return
> different numbers of rows, the shorter results are extended to match
> the longest result by adding nulls. Previously the results were cycled
> until they all terminated at the same time, producing a number of rows
> equal to the least common multiple of the functions' periods. In
> addition, set-returning functions are now disallowed within CASE and
> COALESCE constructs. For more information see Section 37.4.8.
>
> > - Is there a way to change the behaviour of pgsql to produce output like
> > 9.6?
> > - A smooth sql-workaround?
>
> LATERAL would probably help you; see the examples in 37.4.8.
>
> https://www.postgresql.org/docs/10/static/xfunc-sql.html#
> XFUNC-SQL-FUNCTIONS-RETURNING-SET
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-04-01 20:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-01 19:38 select unnest(), unnest() Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2018-04-01 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-04-01 20:25 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox