Re: [GENERAL] How is the right query for this condition ?

2009-11-24 Thread Bino Oetomo 

Harald Fuchs wrote:



For larger tables where an index search would be useful, check out
pgfoundry.org/projects/prefix:

...
  

Wow ... yet another enlightment

Thankyou, I realy appreciate

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

Re: [GENERAL] How is the right query for this condition ?

2009-11-23 Thread Harald Fuchs 
In article 5a9699850911222009j272071fbi1dd0c40dfdf62...@mail.gmail.com,
Brian Modra epai...@googlemail.com writes:

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How is the right query for this condition ?

2009-11-22 Thread Bino Oetomo 

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

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

Re: [GENERAL] How is the right query for this condition ?

2009-11-22 Thread Brian Modra 
2009/11/23 Bino Oetomo b...@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/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How is the right query for this condition ?

2009-11-22 Thread Bino Oetomo 

Dear Sir
Brian Modra wrote:

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;
  

...

I Just try your solution , and it's work like a charm

Thankyou for your enlightment

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