From: | Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How is the right query for this condition ? |
Date: | 2009-11-23 16:04:55 |
Message-ID: | pu1vjpgrbs.fsf@srv.protecting.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In article <5a9699850911222009j272071fbi1dd0c40dfdf62311(at)mail(dot)gmail(dot)com>,
Brian Modra <epailty(at)googlemail(dot)com> writes:
> 2009/11/23 Bino Oetomo <bino(at)indoakses-online(dot)com>:
>> Dear All
>>
>> Suppose I created a database with single table like this :
>> ------start----------
>> CREATE DATABASE bino;
>> CREATE TABLE myrecords(record text);
>> ------end------------
>>
>> and I fill myrecords with this :
>> ------start----------
>> COPY myrecords (record) FROM stdin;
>> 1
>> 12
>> 123
>> 1234
>> \.
>> ------end------------
>>
>> In my bash script, I have variable called 'vseek', that will be use for
>> query parameter.
>> How to query the table , for (i.e):
>>
>> a. If vseek = '127' , I want the result is ==> '12'
>> b. if vseek = '123987' , I want the result is ==> '123'
>> c. if vseek = '14789' , I want the result is ==> '1'
>>
>> Kindly please give me any enlightment
> You can use a plpgsql to do that e.g.
> create or replace function getMatchingRecord(vseek text)
> ...
For larger tables where an index search would be useful, check out
pgfoundry.org/projects/prefix:
CREATE TABLE myrecords (
record prefix_range NOT NULL,
PRIMARY KEY (record)
);
COPY myrecords (record) FROM stdin;
1
12
123
1234
\.
SELECT id, record
FROM myrecords
WHERE record @> '127'
ORDER BY length(record::text) DESC
LIMIT 1;
SELECT id, record
FROM myrecords
WHERE record @> '123987'
ORDER BY length(record::text) DESC
LIMIT 1;
SELECT id, record
FROM myrecords
WHERE record @> '14789'
ORDER BY length(record::text) DESC
LIMIT 1;
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2009-11-23 16:06:56 | Comprehensive operator list |
Previous Message | Tom Lane | 2009-11-23 15:35:06 | Re: [HACKERS] Updating column on row update |