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.
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?
For example:
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 );
The first INSERT fails with the message:
ERROR: INSERT has more expressions than target columns.
Fails same with or without array[] construct.
My real-world usage is significantly more complex, with the composite containing other composites, and many many fields.
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.
I am using perl(5.14.2)
and DBI(1.616)
and psql(9.1.7)
.
MORE INFO:
The following works, but how do I change it so that I do not need to specify ALL fields of book:
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;