Обсуждение: rows and array

hi

does postgresql allows to store rows into array ?
i select rows in a table and i would like to sort them depending on my criteria.

thanks

        Will

FERREIRA William (COFRAMI) wrote:
> hi
>
> does postgresql allows to store rows into array ?
> i select rows in a table and i would like to sort them depending on my
> criteria.

Is this different from
   SELECT a,b,c FROM t ORDER BY a ASC, b DESC;

If so, could you specify more details - what language you are using for
example.

--
   Richard Huxton
   Archonet Ltd

i want do to it in a stored procedure using the pl/pgSQL language.
i can't use "ORDER by" because the traitment is very specific.

for example, in oracle syntaxe i can create my own type :
        type recordChild is TABLE OF XDB_CHILD%ROWTYPE
and next when i declare my variable with this type :
        children recordChild;
i can store rows and re-order them like this :
children(i-j) := children(i-j-1);

-----Message d'origine-----
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : mardi 22 février 2005 12:20
À : FERREIRA William (COFRAMI)
Cc : 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] rows and array

FERREIRA William (COFRAMI) wrote:
> hi
>
> does postgresql allows to store rows into array ?
> i select rows in a table and i would like to sort them depending on my
> criteria.

Is this different from
   SELECT a,b,c FROM t ORDER BY a ASC, b DESC;

If so, could you specify more details - what language you are using for
example.

--
   Richard Huxton
   Archonet Ltd

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.

FERREIRA William (COFRAMI) wrote:
> i want do to it in a stored procedure using the pl/pgSQL language.
> i can't use "ORDER by" because the traitment is very specific.
>
> for example, in oracle syntaxe i can create my own type :
>     type recordChild is TABLE OF XDB_CHILD%ROWTYPE
> and next when i declare my variable with this type :
>     children recordChild;
> i can store rows and re-order them like this :
> children(i-j) := children(i-j-1);

Well, I'd look at one of three solutions:
  1. Functional index to order by
  2. Temporary table (read up on EXECUTE and OID caching)
  3. pl/perl/python (which are better suited to this sort of thing)

Probably worth looking into functional indexes - might be what you need.
--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> FERREIRA William (COFRAMI) wrote:
>> i can store rows and re-order them like this :
>> children(i-j) := children(i-j-1);

> Well, I'd look at one of three solutions:
>   1. Functional index to order by
>   2. Temporary table (read up on EXECUTE and OID caching)
>   3. pl/perl/python (which are better suited to this sort of thing)

I think what he's saying is he wants to do the re-ordering on the client
side.  This seems trivial enough.  If I were doing it against a libpq
result set, I'd probably not bother to sort the physical rows; I'd just
make an array of integers representing row numbers in the PGresult and
sort the row numbers, then use those numbers to pull out the fields when
it came time to display 'em.  (Another way to say that is that a
PGresult *is* an array, it's just got a funny access syntax.)

            regards, tom lane

i don't want to do the re-ordering on the client side.
i want a stored procedure which extract some rows from a database into an array.
next i have a function for re-ordering the array depending on specific needs
and finally generate a XML file on the server

-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]De la part de Tom Lane
Envoyé : mardi 22 février 2005 16:29
À : Richard Huxton
Cc : FERREIRA William (COFRAMI); 'pgsql-general@postgresql.org'
Objet : Re: [GENERAL] rows and array

Richard Huxton <dev@archonet.com> writes:
> FERREIRA William (COFRAMI) wrote:
>> i can store rows and re-order them like this :
>> children(i-j) := children(i-j-1);

> Well, I'd look at one of three solutions:
>   1. Functional index to order by
>   2. Temporary table (read up on EXECUTE and OID caching)
>   3. pl/perl/python (which are better suited to this sort of thing)

I think what he's saying is he wants to do the re-ordering on the client
side.  This seems trivial enough.  If I were doing it against a libpq
result set, I'd probably not bother to sort the physical rows; I'd just
make an array of integers representing row numbers in the PGresult and
sort the row numbers, then use those numbers to pull out the fields when
it came time to display 'em.  (Another way to say that is that a
PGresult *is* an array, it's just got a funny access syntax.)

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

This mail has originated outside your organization,
either from an external partner or the Global Internet.
Keep this in mind if you answer this message.