Re: access sub elements using any()

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: Torsten Grust <teggy(at)fastmail(dot)com>, pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Re: access sub elements using any()
Date: 2022-10-31 22:40:20
Message-ID: f4c25456-1c4c-a279-03d8-3675fe9c2cf3@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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
> | teggy(at)fastmail(dot)com <mailto:teggy(at)fastmail(dot)com>
>
>
Thank you!

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2022-11-01 00:50:06 Re: access sub elements using any()
Previous Message Torsten Grust 2022-10-31 22:36:56 Re: access sub elements using any()