Subject: Array function

From: David Fetter <[email-masked]>
Date: Sun, 31 Oct 2004 16:36:14 -0800

Folks,

Here's a little addition to the array functions & operators section of
the manual.  It adds a function array() to the list.

Cheers,
D
-- 
David Fetter [email-masked] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.221
diff -u -r1.221 func.sgml
--- doc/src/sgml/func.sgml	26 Oct 2004 22:16:11 -0000	1.221
+++ doc/src/sgml/func.sgml	1 Nov 2004 00:33:22 -0000
@@ -6928,6 +6928,20 @@
 	<entry><literal>string_to_array( 'xx~^~yy~^~zz', '~^~')</literal></entry>
 	<entry><literal>{xx,yy,zz}</literal></entry>
        </row>
+       <row>
+	<entry>
+     <literal>
+      <function>array</function>
+      (single-column SELECT)
+     </literal>
+    </entry>
+	<entry><type>anyarray</type></entry>
+    <entry>
+     turns a single-column <literal>SELECT</literal> statement into an array
+    </entry>
+    <entry><literal>array(SELECT foo FROM bar)</literal></entry>
+    <entry><literal>{1,2,3}</literal></entry>
+       </row>
       </tbody>
      </tgroup>
     </table>

---

From: Tom Lane <[email-masked]>
Date: Sun, 31 Oct 2004 22:40:47 -0500

> the manual.  It adds a function array() to the list.

The ARRAY() construct isn't really a function; it is a special syntax
documented in Array Constructors,
http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

The addition you propose is misleading, because it would lead people to
expect to find array() in the output of \df, for example.  It might be
reasonable to put in some kind of "see also" cross-reference in the
array functions section, but we shouldn't list array() as though it were
just like every other function in the section.

			regards, tom lane

---

From: David Fetter <[email-masked]>
Date: Sun, 31 Oct 2004 21:21:17 -0800

On Sun, Oct 31, 2004 at 10:40:47PM -0500, Tom Lane wrote:
> The ARRAY() construct isn't really a function; it is a special
> syntax documented in Array Constructors,
> The addition you propose is misleading, because it would lead people
> to expect to find array() in the output of \df, for example.  It
> might be reasonable to put in some kind of "see also"
> cross-reference in the array functions section, but we shouldn't
> list array() as though it were just like every other function in the
> section.

Good point.

Please find attached a different diff :)

Cheers,
D

Remember to vote!
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.221
diff -u -r1.221 func.sgml
--- doc/src/sgml/func.sgml      26 Oct 2004 22:16:11 -0000      1.221
+++ doc/src/sgml/func.sgml      1 Nov 2004 05:19:02 -0000
@@ -6931,6 +6931,16 @@
       </tbody>
      </tgroup>
     </table>
+  <para>
+  <emphasis role="bold">NOTE:</emphasis>  Although it is not strictly a function
+    and does not appear in \df in psql, <command>ARRAY()</command>,
+    mentioned in <xref linkend="sql-syntax-array-constructors"> acts much
+    like a <literal>table function</literal> (equivalently, a
+    <literal>set-returning function</literal> or a
+    <acronym>SRF</acronym>--see <xref linkend="queries-tablefunctions">)
+    which takes <type>anyarray</type> and returns a set of
+    <type>anyelement</type>.
+  </para>
   </sect1>
  
  <sect1 id="functions-aggregate">

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 1 Nov 2004 20:47:05 +0100

> Please find attached a different diff :)

If you want to write a note, use the <note> element.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---

From: "Jim C. Nasby" <[email-masked]>
Date: Mon, 1 Nov 2004 13:59:13 -0600

BTW, it seems it would be useful to dedicate a chapter to array
handling, since it's something that's not very common amongst databases.

-- 
Jim C. Nasby, Database Consultant               [email-masked] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---

From: David Fetter <[email-masked]>
Date: Mon, 1 Nov 2004 12:21:53 -0800

> If you want to write a note, use the <note> element.

Thanks for the tip :)

Fixed, attaching a diff against CVS HEAD.

Cheers,
D

Remember to vote!
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.221
diff -u -r1.221 func.sgml
--- doc/src/sgml/func.sgml	26 Oct 2004 22:16:11 -0000	1.221
+++ doc/src/sgml/func.sgml	1 Nov 2004 20:21:02 -0000
@@ -6931,6 +6931,18 @@
       </tbody>
      </tgroup>
     </table>
+  <note>
+  <para>
+    Although it is not strictly a function and does not appear in \df
+    in psql, <command>ARRAY()</command>, mentioned in <xref
+    linkend="sql-syntax-array-constructors"> acts much like a
+    <literal>table function</literal> (equivalently, a
+    <literal>set-returning function</literal> or a
+    <acronym>SRF</acronym>--see <xref linkend="queries-tablefunctions">)
+    which takes <type>anyarray</type> and returns a set of
+    <type>anyelement</type>.
+  </para>
+  </note>
   </sect1>

---

From: David Fetter <[email-masked]>
Date: Mon, 1 Nov 2004 12:38:47 -0800

On Mon, Nov 01, 2004 at 01:59:13PM -0600, Jim C. Nasby wrote:
> > Here's a little addition to the array functions & operators
> > section of the manual.  It adds a function array() to the list.
> > 
> > Cheers, D
>

I guess it depends what you mean. Oracle has "subtables," and other
things have stuff like that.  Also, it's in the SQL standard...

Anyhow, where do you think it should go?

Cheers,
D

Remember to vote!

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 1 Nov 2004 23:11:31 +0100

> Anyhow, where do you think it should go?

In the chapter on data types -- where it already is.

---

From: "Jim C. Nasby" <[email-masked]>
Date: Tue, 2 Nov 2004 14:43:42 -0600

The datatypes chapter mentions nothing about ARRAY(), for starters. I'm
not suggesting that the reference is incomplete, but I think a chapter
explaining some about arrays, how to use them, and what you can do with
them would be useful.

On Mon, Nov 01, 2004 at 11:11:31PM +0100, Peter Eisentraut wrote:
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to [email-masked]
>

---

From: "Jim C. Nasby" <[email-masked]>
Date: Tue, 2 Nov 2004 17:31:36 -0600

Here's an example I just thought of that would be great to document:

I have an array of integers. I'd like to also associate some names with
them. I can't do this in the same array, but I can do it in a different
array. But how do I ensure that when I build that array using
name=ARRAY(SELECT name FROM table WHERE id = ANY(id_array) ) that name[1]
actually corresponds to id_array[1]?

On Tue, Nov 02, 2004 at 02:43:42PM -0600, Jim C. Nasby wrote:
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org
>

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 27 Nov 2004 23:06:09 -0500 (EST)

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

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

> Fixed, attaching a diff against CVS HEAD.

[ Attachment, skipping... ]

> TIP 7: don't forget to increase your free space map settings

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  [email-masked]               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 27 Nov 2004 23:08:01 -0500 (EST)

FYI, this discussion about adding a separate array section to the docs
(which we already have, as Peter pointed out) is independent of the
ARRAY() documentation addition I just put in the patch queue.

David Fetter wrote:
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 2 Dec 2004 16:04:20 -0500 (EST)

Patch applied.  Thanks.