George Ant wrote > Hey Guys, > > I have a table(Orders_object_table) of this type: > > CREATE TYPE orders_type AS > (orderid integer, > amount amount_type, > customerid integer, > orderdate date, > orderlines orderlines_type[]); > > and I am trying to insert data from another tables(Orders and Orderlines). > Each Order has many Orderlines but I dont know the number. > > I use this : > > CREATE OR REPLACE FUNCTION Copy_Orders_Data() RETURNS integer as $BODY$ > BEGIN > > INSERT INTO "Orders_object_table" (...,orderlines,...) > SELECT ...,ARRAY[row(ol."OrderlineId", ol."Quantity", > ol."Prod_id")::orderlines_type], ... > FROM "Orders" o > INNER JOIN "Orderlines" ol > ON o."OrderId" = ol."OrderId" > WHERE o."OrderId" >=1 AND o."OrderId" <=12000; > END; > > but it gives me an error. (IT tries to create many rows for each Order > which returns duplicate PK OrderId) > > How can I find how many orderlines have each row, and then insert them in > one row? Each row of the Orders_object_table must have a unique PK OrderId > , and a column with all the Orderlines this Order has. > > Thank you in advance :) > > Kind Regards, > George Ant Use either: Array( sub-select ) or Array_agg( col ) w/ a GROUP BY query David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791092.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
