Re: How is the right query for this condition ?

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)
returns text as $$
declare
str text;
len integer;
ret text;
begin
len := char_length(vseek);
loop
exit when len = 0;
str := substring(vseek from 1 for len);
select "record" into ret from myrecords where "record" = str;
if found then
return ret;
end if;
len := len - 1;
end loop;
end;
$$ language plpgsql;

Then call it as so:

KarooDB=> select getMatchingRecord('127');
getmatchingrecord
-------------------
12
(1 row)

KarooDB=> select getMatchingRecord('123987');
getmatchingrecord
-------------------
123
(1 row)

> Sincerely
> -bino-
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Brian Modra Land line: +27 23 5411 462
Mobile: +27 79 69 77 082
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/