How is the right query for this condition ?

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/

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-

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;

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-

Dear All
Harald Fuchs wrote:

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
\.

I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb
I install it using dpkg , and run the prefix.sql

Create database .. named 'prefbino', and

CREATE TABLE myrecords (
record prefix_range NOT NULL,
PRIMARY KEY (record)
);

Looks good, next
I try to create some records, But I got this error :
-----------------------START------------------
prefbino=# COPY myrecords (record) FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1
12
123
1234
\.

ERROR: duplicate key value violates unique constraint "myrecords_pkey"
CONTEXT: COPY myrecords, line 2: "12"

-----------------------STOP------------------

Kindly please give me further enlightment

Sincerely
-bino-

In article <4B0BBC8E.6010803@indoakses-online.com>,
Bino Oetomo <bino@indoakses-online.com> writes:

I downloaded pgfoundry's prefix, postgresql-8.3-prefix_1.0.0-1_i386.deb
I install it using dpkg , and run the prefix.sql

Create database .. named 'prefbino', and

CREATE TABLE myrecords (
record prefix_range NOT NULL,
PRIMARY KEY (record)
);

Looks good, next
I try to create some records, But I got this error :
-----------------------START------------------
prefbino=# COPY myrecords (record) FROM stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1
12
123
1234
\.

ERROR: duplicate key value violates unique constraint "myrecords_pkey"
CONTEXT: COPY myrecords, line 2: "12"

-----------------------STOP------------------

Kindly please give me further enlightment

At least in prefix 1.0.0 unique indexes seem to be broken. Just drop
the primary key and add a separate index:

CREATE INDEX myrecords_record_ix ON myrecords USING gist (record);

Dear Harald
Harald Fuchs wrote:

At least in prefix 1.0.0 unique indexes seem to be broken. Just drop
the primary key and add a separate index:

CREATE INDEX myrecords_record_ix ON myrecords USING gist (record);

Yup .. it works now.

Thankyou for your enlightment

Sincerely
-bino-

Hi,

Bino Oetomo <bino@indoakses-online.com> writes:

ERROR: duplicate key value violates unique constraint "myrecords_pkey"
CONTEXT: COPY myrecords, line 2: "12"

I think I should add the following code comment to the documentation, if
not already done:

/*
* We invent a prefix_range ordering for convenience, but that's
* dangerous. Use the BTree opclass at your own risk.
*
* On the other hand, when your routing table does contain pretty static
* data and you test it carefully or know it will fit into the ordering
* simplification, you're good to go.
*
* Baring bug, the constraint is to have non-overlapping data.
*/

You have to remember that '12'::prefix_range could be spelled as the
regular expression '12.*'. So that '1'::prefix_range contains '12'.

The BTree opclass is not made to resist to overlapping data. Maybe in
this case though we could say that 12 contains less elements than 1 so
it's less than 1. Here's a test to redefine the pr_cmp() operator in
term of that, as a patch against current CVS (which is 1.0.0).

Can you test with this version and maybe better data set?

Note that as said earlier the indexing you need to speed up queries is
the GiST one, but it could be you want the PK constraint noneless.

prefix=# select prefix_range_cmp('1', '12');
prefix_range_cmp
------------------
1 -- it is 0 without the patch.
(1 row)

This means '1'::prefix_range > '12'::prefix_range and you're now able to
create your PRIMARY KEY on the example data. It's still not very useful
for the general case, but could be argued as better...

Of course changing that will discard any btree containing a prefix_range
column, so that's going to be 1.1.0 if workable.

Regards,
--
dim

PS: no worry about the operators themselves, they are defined atop cmp:

static inline
bool pr_lt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp <= 0 : cmp < 0;
}

static inline
bool pr_gt(prefix_range *a, prefix_range *b, bool eqval) {
int cmp = pr_cmp(a, b);
return eqval ? cmp >= 0 : cmp > 0;
}

In article <87tywid19x.fsf@hi-media-techno.com>,
Dimitri Fontaine <dfontaine@hi-media.com> writes:

The BTree opclass is not made to resist to overlapping data. Maybe in
this case though we could say that 12 contains less elements than 1 so
it's less than 1. Here's a test to redefine the pr_cmp() operator in
term of that, as a patch against current CVS (which is 1.0.0).

Can you test with this version and maybe better data set?

Looks good.

Note that as said earlier the indexing you need to speed up queries is
the GiST one, but it could be you want the PK constraint noneless.

Indeed - I think it's a good thing to be able to prevent something
like

INSERT INTO myrecords (record) VALUES ('12'), ('12');

Oh, here's another gripe: how about renaming README.txt to README.prefix
and removing TESTS.* from "make install"?

Hi,

Le 23 nov. 2009 à 17:04, Harald Fuchs a écrit :

SELECT id, record
FROM myrecords
WHERE record @> '127'
ORDER BY length(record::text) DESC
LIMIT 1;

In prefix 1.0.0 you can say ORDER BY length(record) DESC directly...
--
Dimitri Fontaine
PostgreSQL DBA, Architecte

Hi,

Dimitri Fontaine <dfontaine@hi-media.com> writes:

Can you test with this version and maybe better data set?

[...]

Of course changing that will discard any btree containing a prefix_range
column, so that's going to be 1.1.0 if workable.

http://github.com/dimitri/prefix
http://github.com/dimitri/prefix/commit/e1bcb8e28305c9257655548a70c9fc05a21e9a1e

It's being uploaded to debian now. I'm going to release 1.1.0 source
tarball (only) on pgfoundry.

Regards,
--
dim