In article <5a9699850911222009j272071fbi1dd0c40dfdf62311@xxxxxxxxxxxxxx>, Brian Modra <epailty@xxxxxxxxxxxxxx> writes: > 2009/11/23 Bino Oetomo <bino@xxxxxxxxxxxxxxxxxxxx>: >> 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; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
