public inbox for [email protected]
help / color / mirror / Atom feedaccess sub elements using any()
4+ messages / 3 participants
[nested] [flat]
* access sub elements using any()
@ 2022-10-31 17:04 Rob Sargent <[email protected]>
2022-10-31 22:36 ` Re: access sub elements using any() Torsten Grust <[email protected]>
2022-11-01 00:50 ` Re: access sub elements using any() Tom Lane <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Rob Sargent @ 2022-10-31 17:04 UTC (permalink / raw)
To: pgsql-sql <[email protected]>
Given an array of arrays ( e.g. v = {{1,'a'},{2,'b'},{3,'c'},{2,'d'}} )
is it possible, in plain sql, to access the first element of the listed
arrays using the IN function? Say I wanted just those with 2 as first
element. "where 2 = any(v[1])" does not work and not sure it's
supported. And I cannot craft a lhs to fussy-match each of the
elements in v. Can this be done?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: access sub elements using any()
2022-10-31 17:04 access sub elements using any() Rob Sargent <[email protected]>
@ 2022-10-31 22:36 ` Torsten Grust <[email protected]>
2022-10-31 22:40 ` Re: access sub elements using any() Rob Sargent <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Torsten Grust @ 2022-10-31 22:36 UTC (permalink / raw)
To: Rob Sargent <[email protected]>; pgsql-sql <[email protected]>
Hi,
On Mon, Oct 31, 2022, at 18:04, Rob Sargent wrote:
> Given an array of arrays ( e.g. v = {{1,'a'},{2,'b'},{3,'c'},{2,'d'}} ) is it possible, in plain sql, to access the first element of the listed arrays using the IN function? Say I wanted just those with 2 as first element. "where 2 = any(v[1])" does not work and not sure it's supported. And I cannot craft a lhs to fussy-match each of the elements in v. Can this be done?
if your inner arrays would be row values instead, i.e. if v would read
v = array[(1,'a'),(2,'b'),(3,'c'),(2,'d')] :: t[]
assuming CREATE TYPE t AS (x int, y text);
then your query could simply be
SELECT r.*
FROM unnest(v) AS r
WHERE r.x = 2;
Cheers,
—Torsten
--
| Torsten Grust
| [email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: access sub elements using any()
2022-10-31 17:04 access sub elements using any() Rob Sargent <[email protected]>
2022-10-31 22:36 ` Re: access sub elements using any() Torsten Grust <[email protected]>
@ 2022-10-31 22:40 ` Rob Sargent <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Rob Sargent @ 2022-10-31 22:40 UTC (permalink / raw)
To: Torsten Grust <[email protected]>; pgsql-sql <[email protected]>
On 10/31/22 16:36, Torsten Grust wrote:
> Hi,
>
> On Mon, Oct 31, 2022, at 18:04, Rob Sargent wrote:
>> Given an array of arrays ( e.g. v = {{1,'a'},{2,'b'},{3,'c'},{2,'d'}}
>> ) is it possible, in plain sql, to access the first element of the
>> listed arrays using the IN function? Say I wanted just those with 2
>> as first element. "where 2 = any(v[1])" does not work and not sure
>> it's supported. And I cannot craft a lhs to fussy-match each of the
>> elements in v. Can this be done?
>
> if your inner arrays would be row values instead, i.e. if v would read
>
> v = array[(1,'a'),(2,'b'),(3,'c'),(2,'d')] :: t[]
> assuming CREATE TYPE t AS (x int, y text);
>
> then your query could simply be
>
> SELECT r.*
> FROM unnest(v) AS r
> WHERE r.x = 2;
>
> Cheers,
> —Torsten
>
> --
> | Torsten Grust
> | [email protected] <mailto:[email protected]>
>
>
Thank you!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: access sub elements using any()
2022-10-31 17:04 access sub elements using any() Rob Sargent <[email protected]>
@ 2022-11-01 00:50 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-11-01 00:50 UTC (permalink / raw)
To: Rob Sargent <[email protected]>; +Cc: pgsql-sql <[email protected]>
Rob Sargent <[email protected]> writes:
> Given an array of arrays ( e.g. v = {{1,'a'},{2,'b'},{3,'c'},{2,'d'}} )
> is it possible, in plain sql, to access the first element of the listed
> arrays using the IN function?
If it's actually a 2-D array, and not an array-of-composite which is
what your sample data seems to suggest, then array slicing might help:
regression=# select ('{{1,2},{3,4},{5,6}}'::int[])[:][1];
int4
---------------
{{1},{3},{5}}
(1 row)
regression=# select 2 = any (('{{1,2},{3,4},{5,6}}'::int[])[:][1]);
?column?
----------
f
(1 row)
regression=# select 3 = any (('{{1,2},{3,4},{5,6}}'::int[])[:][1]);
?column?
----------
t
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-11-01 00:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-31 17:04 access sub elements using any() Rob Sargent <[email protected]>
2022-10-31 22:36 ` Torsten Grust <[email protected]>
2022-10-31 22:40 ` Rob Sargent <[email protected]>
2022-11-01 00:50 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox