Dynamic insert into ARRAY? plpgsql - Mailing list pgsql-general

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.