From: | George Ant <g(dot)antonopoulos000(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Dynamic insert into ARRAY? plpgsql |
Date: | 2014-02-08 20:35:49 |
Message-ID: | 1391891749733-5791090.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2014-02-08 20:41:36 | Re: Dynamic insert into ARRAY? plpgsql |
Previous Message | George Ant | 2014-02-08 20:19:49 | Re: PL/pgSQL Copy data from one table to another |