Thread: Can I search for an array in csf?

One field of a table stores an array of characters in a string fromat as "a,b,c,d". Is anyway to apply a select
statement
 
without using stored procedure?

Thanks for your input.

Vernon

On Friday 18 Oct 2002 1:58 pm, Vernon Wu wrote:
> One field of a table stores an array of characters in a string fromat as
> "a,b,c,d". Is anyway to apply a select statement without using stored
> procedure?
>
> Thanks for your input.

Not really,  and I can't think any way of accessing an index on this either.
Are you sure you wanted the characters stored this way? Even if the overhead
of a separate table isn't woth it, you might want to look into arrays and the
intarray stuff in contrib/

--  Richard Huxton

Vernon,

> Thanks Josh, I will take a look at this book. 
> 
> The only problem with the implemention I can see so far is
> performance: change format when read/write data. Other 
> than that, I don't see a big problem. I, however, shall say the
> performance is important in my project. 

I'd think Java would be able to do array --> table and table --> array
very quickly.  Certainly, PHP and Perl do.

Also, you can use a custom aggregate to give yourself a comma-delimited
list from a subtable (see the article on custom aggregates at
techdocs.postgresql.org)   However, custom aggregates are slow and
Java's array-handling is probably faster.

-Josh Berkus

Hi, Christoph,

Thanks for reminding me regular expression. 

The background of my question is about attributes of one element, say hobby for example. Different people have 
different hobbies. Inside of the application, hobbies are denoted in various characters. The selection I mentioned in
the
 
original mail refers to finding people who have certain hobbies in the case.

I think regular expression is the most effective way for the usage.

Thanks again.

Vernon


10/22/2002 5:50:22 AM, Christoph Haller <ch@rodos.fzk.de> wrote:

>>
>> I thought I had made my case clear. Let me rephrase it.
>>
>> I have a character array, {'a', 'b', 'c', 'd'} for example. And this
>array is stored in a DB table field as a string
>of "a,b,c,d".
>> Now, I want to find out whether the table field, or array, contains
>any character set of {'c', 'e', 'h'}. My question
> is
>> whether SQL statement is appliable for this selection, or the data has
>been retrieved and process in the application
>> level.
>>
>> I hope I make th case clear this time.
>>
>Ok, now I see.
>Maybe the Postgres POSIX Regular Expressions are what you are looking
>for.
>Suppose your array "a,b,c,d" is stored into a table field of type
>character,
>character varying or text.
>Then, if you are searching for all entries containing a 'c', 'e',  or
>'h' character
>your SELECT statement would look like
>
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ '[ceh]' ;
>
>Still not sure if you mean a sequence like "c,e,h" on the other hand.
>Then
>SELECT col1, col2, ... FROM table1 WHERE arrayfield ~ 'c,e,h' ;
>should do the trick - as long as the order of characters is identical.
>A sequence of "e,c,h" would not show up, of course.
>
>Regular Expressions are far more powerful than these two examples can
>show.
>Refer to the related chapter in the documentation.
>I hope this helps more than the other replies you've received telling
>you
>'Learn about DB-design first'.
>
>Regards, Christoph
>
>