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
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090.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
