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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:
/messages/by-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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:
/messages/by-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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general