Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 9C1E26349A3
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 12 May 2009 05:40:24 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 24808-01 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue, 12 May 2009 05:40:21 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from wf-out-1314.google.com (wf-out-1314.google.com
 [209.85.200.172])
	by mail.postgresql.org (Postfix) with ESMTP id 42B01634975
	for <pgsql-sql@postgresql.org>; Tue, 12 May 2009 05:40:03 -0300 (ADT)
Received: by wf-out-1314.google.com with SMTP id 28so2490748wfc.28
	for <pgsql-sql@postgresql.org>; Tue, 12 May 2009 01:40:02 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:content-type;
	bh=m7w/OcTB5is7CSB88DZwvmyvpR1mRtxsYdYLD7qAbts=;
	b=DVUtkeOp8lnsNJdJIA6dF7ZBf9G4Fr3+jZULc8/a4itFoNX/s226Ts3j0hcMrDuo0f
	5N5A+wBlzu8Xfz0Q0dED5hvWh3nnVe3rOCmeQBEAVSSFXFOkaYOx1JRp+lds1b0E2xpJ
	Lf8KoqODlOJpOOdlh/Vi52SZO/xx6Pa5jQD68=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type;
	b=pGFBnYWTTVd0hxozhxObJAyPiS5HV+ZjebzeIu4J0WgQpAnzhdCRHVz9dKQfoOI6Lu
	iKtf1tNQtY9ujTXCVrNM+XeIJt4YBgsfbMkMQ++aPwdmP7dmEoMXr4SX5Kis6rBjYita
	Y5acVsI/WJeEjFhhZx3kwKZtQ27j0JGmkuT4c=
MIME-Version: 1.0
Received: by 10.142.77.7 with SMTP id z7mr1222435wfa.175.1242117602723; Tue,
	12 May 2009 01:40:02 -0700 (PDT)
In-Reply-To: <162867790905120128h1dff1ba3mdc9c511af938e595@mail.gmail.com>
References: <7c33d060905120105y74b0483eyf5d94c07912a0702@mail.gmail.com>
	<162867790905120128h1dff1ba3mdc9c511af938e595@mail.gmail.com>
Date: Tue, 12 May 2009 15:40:02 +0700
Message-ID: <7c33d060905120140p78b4e180rda6101cff30459eb@mail.gmail.com>
Subject: Re: Array: comparing first N elements?
From: David Garamond <davidgaramond@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>, pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=001636e90f6c7ac8a40469b30a1e
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200905/53
X-Sequence-Number: 32545

--001636e90f6c7ac8a40469b30a1e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

On Tue, May 12, 2009 at 3:28 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

> Hello
>
> create or replace function eqn(anyarray, anyarray, int)
> returns boolean as $$
>  select not exists(select $1[i] from generate_series(1,$3) g(i)
>                           except
>                           select $2[i] from generate_series(1,$3) g(i))
> $$ language sql immutable strict;
>
> postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 3);
>  eqn
> -----
>  t
> (1 row)
>
> Time: 1,590 ms
> postgres=# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 4);
>  eqn
> -----
>  f
> (1 row)
>

Hi Pavel,

Thanks for the solution, but that's too slow. I'd rather just do this
instead:

select * from product
where parents[1:(select array_length(parents,1) from product where
name='wanted')+1]=
  (select parents from product where name='wanted')||
  (select id from product where name='wanted');

but the above query is also unable to use any indices (unlike LIKE 'foo%').

Regards,
Dave

--001636e90f6c7ac8a40469b30a1e
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>On Tue, May 12, 2009 at 3:28 PM, Pavel Stehule <span dir=3D"ltr">&lt;<=
a href=3D"mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>&gt;</=
span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8e=
x;border-left:1px #ccc solid;padding-left:1ex;">
Hello<br>
<br>
create or replace function eqn(anyarray, anyarray, int)<br>
returns boolean as $$<br>
 =A0select not exists(select $1[i] from generate_series(1,$3) g(i)<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 except<br>
 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 select $2[i] from gene=
rate_series(1,$3) g(i))<br>
$$ language sql immutable strict;<br>
<br>
postgres=3D# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 3);<br>
=A0eqn<br>
-----<br>
=A0t<br>
(1 row)<br>
<br>
Time: 1,590 ms<br>
postgres=3D# select eqn(array[1,2,3,4,5], array[1,2,3,5,6], 4);<br>
=A0eqn<br>
-----<br>
=A0f<br>
(1 row)<br>
</blockquote><div><br></div><div>Hi Pavel,</div><div><br></div><div>Thanks =
for the solution, but that&#39;s too slow. I&#39;d rather just do this inst=
ead:</div><div><br></div><div>select * from product<br>where parents[1:(sel=
ect array_length(parents,1) from product where name=3D&#39;wanted&#39;)+1]=
=3D<br>
=A0 (select parents from product where name=3D&#39;wanted&#39;)||<br>=A0 (s=
elect id from product where name=3D&#39;wanted&#39;);<br><br>but the above =
query is also unable to use any indices (unlike LIKE &#39;foo%&#39;).</div>=
<div>
<br></div><div>Regards,</div><div>Dave=A0</div></div>

--001636e90f6c7ac8a40469b30a1e--