title: PostgreSQL - INSERT an array of composite type containing arrays
body: <p>I have a composite type containing arrays of TEXT, etc.  I am using this inside my main table to create an array of composite type.<br>
  How do I generate an INSERT command (without using the default field names of the composite type) ?  Can I create a TEMPORARY TABLE with the array of composites and then insert it into the main table?</p>
  
  <p>For example:</p>
  
  <pre><code>DROP TABLE collection;
  DROP TABLE book_set;
  DROP TYPE book;
  
  CREATE TYPE book AS ( title TEXT, authors TEXT[], extra_spare TEXT );
  CREATE TEMPORARY TABLE book_set ( books book[] );
  CREATE TABLE shelf_collection ( shelf INT, position INT, books book[] );
  
  -- Prefer to specify the fields I want, and NOT extra_spare as shown here!
  -- AND it doesn't yet work... needs more casting?
  INSERT INTO book_set( books ) VALUES (
        ( 'book1', array[ ( 'author1', 'author2' ) ], '' ),
        ( 'book2', array[ ( 'author3' )            ], '' ) ); 
  
  -- And this obviously does not work yet!
  INSERT INTO shelf_collection( shelf, position, books ) VALUES ( 1, 2, book_set ); 
  </code></pre>
  
  <p>The first INSERT fails with the message:   </p>
  
  <blockquote>
    <p>ERROR: INSERT has more expressions than target columns.</p>
  </blockquote>
  
  <p>Fails same with or without array[] construct.</p>
  
  <p>My real-world usage is significantly more complex, with the composite containing other composites, and many many fields.</p>
  
  <p>I am not using multiple tables here for performance reasons (no join required to retrieve), and the inner composites and arrays are never referenced independently.</p>
  
  <p>I am using <code>perl(5.14.2)</code> and <code>DBI(1.616)</code> and <code>psql(9.1.7)</code>.</p>
  
  <hr>
  
  <p><strong>MORE INFO:</strong></p>
  
  <p>The following works, but how do I change it so that I do not need to specify ALL fields of book:</p>
  
  <pre><code>DROP TABLE shelf_collection;
  DROP TYPE book;
  
  CREATE TYPE  book AS          ( title TEXT, authors TEXT[], extra_spare TEXT );
  CREATE TABLE shelf_collection ( shelf INT, position INT, books book[] );
  
  INSERT INTO shelf_collection VALUES ( 12, 23, array[ROW( 'book title 1', array[ 'author1', 'author2' ], '' )::book] );
  
  SELECT * FROM shelf_collection;
  </code></pre>