Re: Dynamic insert into ARRAY? plpgsql

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.