Skip site navigation (1) Skip section navigation (2)

Constructing two-dimensional arrays from a query

From: "David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Constructing two-dimensional arrays from a query
Date: 2007-11-12 18:05:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
My apologies for reposting this.  I never received a reply, and need
to know if I should keep trying to do this or just give up.

Thanks again.



PG 8.2

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).

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,

pgsql-novice by date

Next:From: gokulDate: 2007-11-12 22:20:35
Subject: postgresql 8.1.10-1PGDG (32 bit) installaion on RHEL 4
Previous:From: Hans-Peter OeriDate: 2007-11-12 17:04:54
Subject: Re: array indizes in SQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group