From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200702201426.l1KEQ1O18498@momjian.us>
Subject: Re: can someone explain confusing array indexing
 nomenclature
In-Reply-To: <200702200942.18500.achill@matrix.gatewaynet.com>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Date: Tue, 20 Feb 2007 09:26:01 -0500 (EST)
CC: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="ELM1171981561-7475-0_"
Content-Transfer-Encoding: 7bit


--ELM1171981561-7475-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"


Yes, it is confusing.  I have an update to the array documentation that
should clarify it --- attached.

---------------------------------------------------------------------------

Achilleas Mantzios wrote:
> ???? ????????? 16 ??????????? 2007 20:35, ?/? chrisj ??????:
> > I am quite sure the [2] is not discarded, easy enough to test but I don't
> > have access to PG at the moment.
> 
> Well it should, since
> 
> dynacom=# SELECT 
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
>        text
> -------------------
>  {{meeting,lunch}}
> (1 row)
> 
> dynacom=# SELECT 
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
>     text
> -------------
>  {{meeting}}
> (1 row)
> 
> dynacom=# SELECT 
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
>        text
> -------------------
>  {{meeting,lunch}}
> (1 row)
> 
> dynacom=# SELECT 
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
>        text
> -------------------
>  {{meeting,lunch}}
> (1 row)
> 
> dynacom=# SELECT 
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1000];
>        text
> -------------------
>  {{meeting,lunch}}
> (1 row)
> 
> dynacom=#                            
> 
> >
> > Achilleas Mantzios wrote:
> > > ???? ?????? 15 ??????????? 2007 18:55, ?/? chrisj ??????:
> > >> Thanks Achilleas,
> > >>
> > >> I see what you are saying, but if we consider just the index "[2]" for a
> > >> moment,
> > >> it means something different depending upon the context  (in one case it
> > >> means "2" and in the other case it means "1:2") and the context is
> > >> determined by the format of indexes on other dimensions.
> > >>
> > >> I believe I understand....but incredibly confusing.
> > >
> > > Now that i think about it again, i speculate that the [2] is discarded.
> > >
> > >> - chris
> > >>
> > >> Achilleas Mantzios wrote:
> > >> > ???????? ?????????????? 14 ?????????????????????? 2007 21:31, ??/??
> > >>
> > >> chrisj
> > >
> > > ????????????:
> > >> >> given the following table:
> > >> >>
> > >> >> protocal2=> select * from sal_emp ;
> > >> >>  name  |      pay_by_quarter       |                 schedule
> > >>
> > >> -------+---------------------------+------------------------------------
> > >>
> > >> >>--- ---- Bill  | {10000,10000,10000,10000} |
> > >> >> {{meeting,lunch},{training,presentation}}
> > >> >>  Carol | {20000,25000,25000,25000} |
> > >> >> {{breakfast,consulting},{meeting,lunch}}
> > >> >> (2 rows)
> > >> >>
> > >> >> why do the following two queries yield different results??
> > >> >>
> > >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> > >> >>  schedule
> > >> >> ----------
> > >> >>  lunch
> > >> >> (1 row)
> > >> >>
> > >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> > >> >>      schedule
> > >> >> -------------------
> > >> >>  {{meeting,lunch}}
> > >> >> (1 row)
> > >> >
> > >> > The [n:m] notation denotes a slice of the array (not element).
> > >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> > >> > while schedule[1:1][2] could mean
> > >> > the second row of the subarray schedule[1:1][1:2].
> > >> > So these two are foundamentally different things.
> > >> > In my 7.4 even if you gave
> > >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> > >> > you would still get  {{meeting,lunch}} as a result.
> > >> > (Right or wrong is another story).
> > >> > Anyway the first time you query for a "text",
> > >> > the second time you query for a "text[]", so you should expect
> > >> > different results.
> > >> > --
> > >> > Achilleas Mantzios
> > >> >
> > >> > ---------------------------(end of
> > >>
> > >> broadcast)---------------------------
> > >>
> > >> > TIP 3: Have you checked our extensive FAQ?
> > >> >
> > >> >                http://www.postgresql.org/docs/faq
> > >
> > > --
> > > Achilleas Mantzios
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: Have you checked our extensive FAQ?
> > >
> > >                http://www.postgresql.org/docs/faq
> 
> -- 
> Achilleas Mantzios
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate

-- 
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

--ELM1171981561-7475-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/pgpatches/array"

Index: doc/src/sgml/array.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/array.sgml,v
retrieving revision 1.56
diff -c -c -r1.56 array.sgml
*** doc/src/sgml/array.sgml	1 Feb 2007 00:28:16 -0000	1.56
--- doc/src/sgml/array.sgml	20 Feb 2007 03:43:28 -0000
***************
*** 243,260 ****
  (1 row)
  </programlisting>
  
!   We could also have written:
  
  <programlisting>
- SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
- </programlisting>
- 
-   with the same result.  An array subscripting operation is always taken to
-   represent an array slice if any of the subscripts are written in the form
-   <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
-   A lower bound of 1 is assumed for any subscript where only one value
-   is specified, as in this example:
- <programlisting>
  SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
  
                   schedule
--- 243,256 ----
  (1 row)
  </programlisting>
  
!   If any dimmension is written as a slice, i.e contains a colon, then all
!   dimmensions are treated as slices.  If a dimmension is missing, it is
!   assumed to be <literal>[1:1]</>.  If a dimmension has only a single
!   number (no colon), that dimmension is treated as being from <literal>1</>
!   to the number specified.  For example, <literal>[2]</> is treated as
!   <literal>[1:2], as in this example:
  
  <programlisting>
  SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
  
                   schedule

--ELM1171981561-7475-0_--