Thread: Dynamic insert into ARRAY? plpgsql
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.
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.
You must a) join the 2 tables on the orderID ... where orderID=15 and then GROUP BY the result by the order ID and concat the orderlines by a custom aggregate function like: http://www.postgresql.org/message-id/db7789b.0309131210.625da358@posting.google.com -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791093.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
alexandros_e wrote > You must a) join the 2 tables on the orderID ... where orderID=15 and then > GROUP BY the result by the order ID and concat the orderlines by a custom > aggregate function like: > http://www.postgresql.org/message-id/db7789b.0309131210.625da358@posting.google.com Maybe back in 2003 when that message was written... The OP did join the tables but tried to create an array literal instead of using a now standard array creation function. And you do not need to specify a specific, single, order if you formulate the group by correctly. You should avoid blindly repeating information, especially if it is more than a few years old. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791094.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Thank you for your Response! Inserting a sub-select into the array seems to be the solution that I want, but it gives me this error--> subquery must return only one column Any help? Kind Regards, George Ant -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791101.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
George Ant wrote > Thank you for your Response! > > Inserting a sub-select into the array seems to be the solution that I > want, but it gives me this error--> subquery must return only one column > > Any help? > > Kind Regards, > George Ant In both cases you want to be storing a single composite type column. So your sub-select has to use row(...)::composite_type in the select. Just like your original query did. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Dynamic-insert-into-ARRAY-plpgsql-tp5791090p5791110.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.