array/function question

From: Joshua Berry <yoberi(at)gmail(dot)com>
To: postgresql Forums <pgsql-general(at)postgresql(dot)org>
Subject: array/function question
Date: 2009-05-18 21:39:10
Message-ID: 9824CDCD-B0E3-4C2F-9CA4-5CF454EBD862@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello All,

I'm trying to optimize a few slow queries and helper functions, and
have found a poor performing function. To improve performance, I'd
like to create a function that does the following:

Inputs:
A: an array of integers. for example: { 1, 2, 3, 4, 7 }
B: an array of integers. for example: { 1, 4, 8, 9 }

Returns
C: an array of bools the same dimensions as Array A. In this example:
{ true, false, false, false, true, false }

Effectively, this function would use Array A as a set of boolean tests
to exercise on Array B. The result array will have the save number of
elements as array A.

What I lack is the knowledge of how to
1. index and compare arrays when their input size is not known. (I
only know how to use hardcoded indexes like A[1], B[2], etc.
2. To use control structures for recursion/looping. I've read http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html
but still not sure how to apply the grammar to arrays data types.

If there is a builtin array function that achieves this, that would be
good to know as well.

Cheers,

-Joshua

Joshua Berry

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Paolo Saul 2009-05-18 21:40:30 Re: Is this a bug or a feature? Column visibility in subquery from outer query
Previous Message Marsh Ray 2009-05-18 21:38:36 Commit visibility guarantees