Re: How is the right query for this condition ? - Mailing list pgsql-general

In article <5a9699850911222009j272071fbi1dd0c40dfdf62311@mail.gmail.com>,
Brian Modra <epailty@googlemail.com> writes:

> 2009/11/23 Bino Oetomo <bino@indoakses-online.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;