Thread:
Hello!! in flagship ( a variant of clipper for unix , http://www.wgs.com/) it is an interesting statement "seek eval" ; it scans the index and for every position it evaluates a block of code/ function until the function return true . is in postgresql an internal mechanism or a way to use the index when selecting records with conditions like function(index_expression) = value ? Thanks!
Robert Deme wrote: > > Hello!! > > in flagship ( a variant of clipper for unix , http://www.wgs.com/) it is > an interesting statement "seek eval" ; it scans the index and for every > position it evaluates a block of code/ function until the function > return true . > is in postgresql an internal mechanism or a way to use the index when > selecting records with conditions like function(index_expression) = > value ? I'm not sure I understand the question. There are a large number of functions built in to PostgreSQL that you can use in your queries. Plus you can define your own functions in SQL, C, or other languages. If you're talking about something else, maybe send an example. DROP TABLE pie; CREATE TABLE pie ( filling text, slice float --size in degrees. ); INSERT INTO pie VALUES ('blackbird', 90); INSERT INTO pie VALUES ('blackbird', 45); INSERT INTO pie VALUES ('plum', 120); SELECT filling, radians( slice ) AS size FROM pie WHERE radians( slice ) > 1; ________________________ Ron Peterson rpeterson@yellowbank.com
Ron Peterson wrote: > > Robert Deme wrote: > > > > Hello!! > > > > in flagship ( a variant of clipper for unix , http://www.wgs.com/) it is > > an interesting statement "seek eval" ; it scans the index and for every > > position it evaluates a block of code/ function until the function > > return true . > > is in postgresql an internal mechanism or a way to use the index when > > selecting records with conditions like function(index_expression) = > > value ? > > I'm not sure I understand the question. There are a large number of > functions built in to PostgreSQL that you can use in your queries. Plus > you can define your own functions in SQL, C, or other languages. If > you're talking about something else, maybe send an example. I think he's asking about functional indexes. PostgreSQL does support functional indexes. For example: stocks=# create table test (field text); CREATE stocks=# create index k_test1 on test ( lower(field) ); CREATE Then a SELECT * FROM test WHERE lower(field) = 'mike' would use the index (if the optimizer so chooses) to locate the appropriate records. Hope that helps, Mike Mascari
it is no doubt that "select ... where function(slice) > 1 " works but my question is if the query uses an index on field "slice" and if I change the function or other parameters of the function if it still uses that index . my problem is with text fields . just as an example: in some application a_text_field is a concatenation of 3 characters for department , 3 character for cost center, other 3 character for location etc ; can i create o single index on the whole a_text_field (not one for department , one for cost center etc) , change the function or other parameters of the function in a "select ... where function(a_text_field,other _param) = value" and the query still uses the index ? my question is not about the functionality , that is ok , it is about the speed/ use of index . thank you for the answers!!! Robert Deme wrote: > > Hello!! > > in flagship ( a variant of clipper for unix , http://www.wgs.com/) it is > an interesting statement "seek eval" ; it scans the index and for every > position it evaluates a block of code/ function until the function > return true . > is in postgresql an internal mechanism or a way to use the index when > selecting records with conditions like function(index_expression) = > value ? I'm not sure I understand the question. There are a large number of functions built in to PostgreSQL that you can use in your queries. Plus you can define your own functions in SQL, C, or other languages. If you're talking about something else, maybe send an example. DROP TABLE pie; CREATE TABLE pie ( filling text, slice float --size in degrees. ); INSERT INTO pie VALUES ('blackbird', 90); INSERT INTO pie VALUES ('blackbird', 45); INSERT INTO pie VALUES ('plum', 120); SELECT filling, radians( slice ) AS size FROM pie WHERE radians( slice ) > 1; ________________________ Ron Peterson rpeterson@yellowbank.com