Hosting Provided By

[NOVICE] Constructing two-dimensional arrays from a query

From: David Monarchi <david.e.monarchi(at)gmail.com>
Date: Thu Nov 08 2007 - 19:13:28 EST

PG 8.2
Linux

Hello -

I would like to update a two-dimensional array field in a table using pure SQL.

The field is defined as text[][]

My query produces data in the form
x, y, z
where x is the value of the key in the table I want to update

         y is the value of the first element of the first subarray
         z is the value of the second element of the first array

For example, part of the result of a query might be

    x1 y1 z1
    x1 y2 z2
    x1 y3 z3

After updating the table, the row would look like this (quotes omitted for clarity).

Do you need help?

x1 {{y1,z1},{y2,z2},{y3,z3}}

In section 4.2.10, an examle is given using the ARRAY constructor, but it explicitly says that the subquery must return a single column. I don't know if this is a limitation of 8.2 or if there is a way around this. The example in the manual is

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');

Any assistance would be greatly appreciated.

Thank you,
David

---------------------------(end of broadcast)---------------------------

TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq Received on Thu Nov 8 19:13:45 2007

This message: [ Message body ]
Next message: Josh Tolley: "Re: [NOVICE] Debugging question ..."
Previous message: Hans-Peter Oeri: "Re: [NOVICE] Obtaining the primary key of the record just inserted"

Contemporary messages sorted: [ By Date ] [ By Thread ] [ By Subject ] [ By Author ] [ By messages with attachments ]

This archive was generated by hypermail 2.1.8 : Thu Jun 19 2008 - 00:03:43 EDT