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

From Brian Modra
Subject Re: How is the right query for this condition ?
Date
Msg-id 5a9699850911222009j272071fbi1dd0c40dfdf62311@mail.gmail.com
Whole thread Raw
In response to How is the right query for this condition ?  (Bino Oetomo <bino@indoakses-online.com>)
Responses Re: How is the right query for this condition ?
List pgsql-general
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)
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@postgresql.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/

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: error linking with PostgreSQL
Next
From: Robert Haas
Date:
Subject: Re: [HACKERS] Updating column on row update