Select Rows in a Postgresql array

Question

Select Rows in a Postgresql array

Asked 10 years ago

Viewed 1,837 times

1

I am using the Postgresql and Python database to query it, I have the following modeling:

tabelas

I’m trying to make an appointment that will return all the OrderItems of a Order in a array, for that I made the following code:

select ARRAY(
    SELECT '(' || translate(string_to_array(x.*::"text", ',')::text, '{()}', '') || ')' 
    from "OrderItem" x
)::"OrderItem"[] AS "_AAA";

What returns me the following result:

"{"(16,1,ProductName1,M,45.00,1,6)","(17,6,ProductName2,M,45.00,1,6)"}"

In Python I’m using psycopg2 to chat with the bank, and so it returns me a string, as in the result above, not being able to iterate through it to pick up the tuples with the values.

Does anyone have any idea how I can do this query to return an Orderitem array ?

Can you share the select you wrote with python? you can try using a Dict-like cursor: http://initd.org/psycopg/docs/extras.html#Dictionary-like-cursor

– Rafael Barros

2014/12/12 at 15:42
The select in python was a test, so it was "SELECT * FROM Nomedaview WHERE "Orderid"=10;". I am using Dict cursor, and returns me a dictionary where the key is "Orderitems" and the value is the result I put there in the post, so it does not do this parse alone.

– will

2014/12/12 at 19:07

1 answer

Browser other questions tagged array python postgresql query

You are not signed in. Login or sign up in order to post.

by Clodoaldo Neto • **1,006** points · Answer 1 · 2014-12-13T08:21:42+00:00

It seems you want to aggregate the table rows orderItem

select array_agg(orderItem) as "_AAA"
from orderItem
where orderId = 10

This should return a list of tuples.

http://www.postgresql.org/docs/current/static/functions-aggregate.html