Re: [HACKERS] Domains and supporting functions
Elein wrote: http://www.varlena.com/GeneralBits/128.php Known Problems and Issues: * Creating the table with an email PRIMARY KEY did not use our comparison function. It was necessary to create a unique index which explicitly used the email operator class. * ORDER BY requires USING op clause. * LIKE does not work. Use defined operator % instead. There are convincing arguments for and against this behavior. Feel free to argue one way or the other. I once created a case-insensitive ivarchar type based just reusing the varcharin/out functions and some pl/pgsql functions. I can send you the complete .sql file, if you want. I have not looked at your type, but when I saw LIKE does not work, I thought I'd send you this part of the ivarchar type, which should explain how I got the LIKE functionality to work. -- Support case insensitive LIKE operations -- Support functions CREATE FUNCTION ivarcharlike( ivarchar, text ) RETURNS boolean AS 'BEGIN RETURN texticlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT; CREATE FUNCTION ivarcharnlike( ivarchar, text ) RETURNS boolean AS 'BEGIN RETURN texticnlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT; -- Operators used by LIKE and NOT LIKE CREATE OPERATOR ~~ ( PROCEDURE=ivarcharlike, LEFTARG=ivarchar, RIGHTARG=text, NEGATOR= !~~, RESTRICT=iclikesel, JOIN=iclikejoinsel ); CREATE OPERATOR !~~ ( PROCEDURE=ivarcharnlike, LEFTARG=ivarchar, RIGHTARG=text, NEGATOR= ~~, RESTRICT=icnlikesel, JOIN=icnlikejoinsel ); LIKE is really not much more than syntactic sugar for the ~~ operator. Hope this is useful. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Domains and supporting functions
On Mon, Feb 20, 2006 at 09:03:29AM +0100, Michael Paesold wrote: Elein wrote: http://www.varlena.com/GeneralBits/128.php Known Problems and Issues: * Creating the table with an email PRIMARY KEY did not use our comparison function. It was necessary to create a unique index which explicitly used the email operator class. * ORDER BY requires USING op clause. * LIKE does not work. Use defined operator % instead. There are convincing arguments for and against this behavior. Feel free to argue one way or the other. I once created a case-insensitive ivarchar type based just reusing the varcharin/out functions and some pl/pgsql functions. I can send you the complete .sql file, if you want. The point of my article is to create the sub type using domains. This technique inherits the input/output routines of the parent type. I have not looked at your type, but when I saw LIKE does not work, I thought I'd send you this part of the ivarchar type, which should explain how I got the LIKE functionality to work. -- Support case insensitive LIKE operations -- Support functions CREATE FUNCTION ivarcharlike( ivarchar, text ) RETURNS boolean AS 'BEGIN RETURN texticlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT; CREATE FUNCTION ivarcharnlike( ivarchar, text ) RETURNS boolean AS 'BEGIN RETURN texticnlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT; -- Operators used by LIKE and NOT LIKE CREATE OPERATOR ~~ ( PROCEDURE=ivarcharlike, LEFTARG=ivarchar, RIGHTARG=text, NEGATOR= !~~, RESTRICT=iclikesel, JOIN=iclikejoinsel ); CREATE OPERATOR !~~ ( PROCEDURE=ivarcharnlike, LEFTARG=ivarchar, RIGHTARG=text, NEGATOR= ~~, RESTRICT=icnlikesel, JOIN=icnlikejoinsel ); LIKE is really not much more than syntactic sugar for the ~~ operator. Unfortunately this does not work for domains. A bug, IMHO. One should be able to override ALL operators for domains. --elein [EMAIL PROTECTED] Hope this is useful. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Domains and supporting functions
On Sat, Feb 18, 2006 at 09:27:47PM -0800, elein wrote: I've got a domain based on a text type. I've overridden the equal operator with lower(text) = lower(text). I created a table containing my new domain type and can see that the equals operator is not being used to determine uniqueness. What you want is citext. http://gborg.postgresql.org/project/citext/projdisplay.php It is a case-insensetive type with indexing and conparison support. Domains arn't going to do what you want... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 01:26:31AM -0500, Tom Lane wrote: elein [EMAIL PROTECTED] writes: I've got a domain based on a text type. I've overridden the equal operator with lower(text) = lower(text). This won't work, you need to make a type instead. Actually I can do and have done this. It is being tested now. I did create an opclass. It creates a UNIQUE index just fine for the type using the lower() functionality. *If* it passes all of my testing I'll publish it tomorrow on general bits. Perhaps folks can help try to break it when I publish (or if you are around today Sunday, you can test in advance--email me directly.) If it doesn't pass the tests, well, I'm hoping it will be functionally close enough for common use. This implementation is all in SQL and plperl--no C code. Now my only complaint is that ORDER BY requires the USING op when it should recognize the information in the opclass for btree and for the type of the sort column. I can explain why it doesn't recognize the opclass information, but I think it should. If this is the way domains really are, I would strongly suggest expanding create domain to merge with create type (under) and allow us to list the basic functions. IMHO, the exact difference between a domain and a type is you get to choose your own definitions of the basic operations on a type. There's no free lunch: as soon as you start substituting operations the complexity involved goes up by an order of magnitude. Type inheritance for base types should work by inheriting all of the parent's definitions and overriding them only as necessary. What I'm trying to do is to demonstrate that we can weasle this functionality using domains. --elein [EMAIL PROTECTED] regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: Actually I can do and have done this. It is being tested now. I did create an opclass. It creates a UNIQUE index just fine for the type using the lower() functionality. *If* it passes all of my testing I'll publish it tomorrow on general bits. Perhaps folks can help try to break it when I publish (or if you are around today Sunday, you can test in advance--email me directly.) How is this different from the citext module I suggested? Now my only complaint is that ORDER BY requires the USING op when it should recognize the information in the opclass for btree and for the type of the sort column. I can explain why it doesn't recognize the opclass information, but I think it should. ORDER BY x ASC is a synonym for ORDER BY x USING . That's the way it is currently. To use ORDER BY by itself you need to call your operators and . Type inheritance for base types should work by inheriting all of the parent's definitions and overriding them only as necessary. What I'm trying to do is to demonstrate that we can weasle this functionality using domains. Well, you can kind of do this by creating an implicit cast from your type to text. Then you can use your type anywhere where text can appear (like strpos, length, etc). Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: Actually I can do and have done this. It is being tested now. I did create an opclass. It creates a UNIQUE index just fine for the type using the lower() functionality. *If* it passes all of my testing I'll publish it tomorrow on general bits. Perhaps folks can help try to break it when I publish (or if you are around today Sunday, you can test in advance--email me directly.) How is this different from the citext module I suggested? My implementation is in SQL and plperl only. Also, the lower case comparisons are only one aspect of the datatype. Now my only complaint is that ORDER BY requires the USING op when it should recognize the information in the opclass for btree and for the type of the sort column. I can explain why it doesn't recognize the opclass information, but I think it should. ORDER BY x ASC is a synonym for ORDER BY x USING . That's the way it is currently. To use ORDER BY by itself you need to call your operators and . This does not work where x is datatype foo with opclass foo_ops. In this case, it uses the text instead of the foo . Type inheritance for base types should work by inheriting all of the parent's definitions and overriding them only as necessary. What I'm trying to do is to demonstrate that we can weasle this functionality using domains. Well, you can kind of do this by creating an implicit cast from your type to text. Then you can use your type anywhere where text can appear (like strpos, length, etc). Yes, I'm leveraging most of the text functions. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: ORDER BY x ASC is a synonym for ORDER BY x USING . That's the way it is currently. To use ORDER BY by itself you need to call your operators and . This does not work where x is datatype foo with opclass foo_ops. In this case, it uses the text instead of the foo . Huh? You must be doing something unusual because it does work normally. Did you specify the opclass as the default for the type? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Domains and supporting functions
Martijn van Oosterhout wrote: On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: Actually I can do and have done this. It is being tested now. I did create an opclass. It creates a UNIQUE index just fine for the type using the lower() functionality. *If* it passes all of my testing I'll publish it tomorrow on general bits. Perhaps folks can help try to break it when I publish (or if you are around today Sunday, you can test in advance--email me directly.) How is this different from the citext module I suggested? Have you looked at the code of citext? Unless I'm misreading, it creates a lowercase copy of each string for each comparison. And it doesn't look to me like it's encoding/locale aware. No doubt it serves the author's needs, but I'd be very careful of using or recommending it for general use. I'm not sure how hard a text type with efficient, encoding and locale aware, case-insensitive comparison would be to create , but it would be a Good Thing (tm) to have available. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote: On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: ORDER BY x ASC is a synonym for ORDER BY x USING . That's the way it is currently. To use ORDER BY by itself you need to call your operators and . This does not work where x is datatype foo with opclass foo_ops. In this case, it uses the text instead of the foo . Huh? You must be doing something unusual because it does work normally. Did you specify the opclass as the default for the type? I'll show you my test case if you'll show me yours :) ~e Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 04:35:56PM -0500, Andrew Dunstan wrote: Have you looked at the code of citext? Unless I'm misreading, it creates a lowercase copy of each string for each comparison. And it doesn't look to me like it's encoding/locale aware. Its cilower function isn't terribly great and could probably do with some work. toupper/tolower() are encoding/locale sensetive, but the code used doesn't really handle multibyte encodings. But it's an excellent starting point for creating new types because almost all the hard work is done. I'm not sure how hard a text type with efficient, encoding and locale aware, case-insensitive comparison would be to create , but it would be a Good Thing (tm) to have available. Hmm, case-insensetive match is a terribly badly defined concept. There's a reason why there's a strcasecmp() but no strcasecoll(). The code currently uses tolower, but if you changed it to do toupper it would be equally valid yet produce different results. If/when we ever get to use a real internationalisation library like ICU, we can do things like convert strings to Normal Form D so we can compare character seperate from their accents, ie accent-insensetive comparison. In any case ICU contains mappings for things like title-case and all the different kinds of space and hyphens so people can specify their own mapping to get whatever they're happy with. Until then, people will just have to rely on their system's support for tolower(). Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 01:36:41PM -0800, elein wrote: On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote: On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: ORDER BY x ASC is a synonym for ORDER BY x USING . That's the way it is currently. To use ORDER BY by itself you need to call your operators and . This does not work where x is datatype foo with opclass foo_ops. In this case, it uses the text instead of the foo . Huh? You must be doing something unusual because it does work normally. Did you specify the opclass as the default for the type? I'll show you my test case if you'll show me yours :) Ok, here's a quick example I whipped up and if you run it it clearly shows it's using the comparison function from the operator class. http://svana.org/kleptog/temp/text2.example It basically replicates the entire infrastructure for the text type as a new type, text2 so there's planty of scope for confusion, but postgresql correctly picks the right function. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 11:34:21PM +0100, Martijn van Oosterhout wrote: On Sun, Feb 19, 2006 at 01:36:41PM -0800, elein wrote: On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote: On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: ORDER BY x ASC is a synonym for ORDER BY x USING . That's the way it is currently. To use ORDER BY by itself you need to call your operators and . This does not work where x is datatype foo with opclass foo_ops. In this case, it uses the text instead of the foo . Huh? You must be doing something unusual because it does work normally. Did you specify the opclass as the default for the type? I'll show you my test case if you'll show me yours :) Ok, here's a quick example I whipped up and if you run it it clearly shows it's using the comparison function from the operator class. http://svana.org/kleptog/temp/text2.example It basically replicates the entire infrastructure for the text type as a new type, text2 so there's planty of scope for confusion, but postgresql correctly picks the right function. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. Comparing test cases we found that Martijn was using a true CREATE TYPE while I am using CREATE DOMAIN. That was the only difference that mattered. So far there are only two gotchas with this exercise of making a domain based base type. 1) LIKE doesn't work. Workaround: create and use like-ish operator. Arguably correct behavior. 2) ORDER BY requires the USING op clause. Workaround: use the USING op clause. --elein ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: On Sun, Feb 19, 2006 at 01:26:31AM -0500, Tom Lane wrote: elein [EMAIL PROTECTED] writes: I've got a domain based on a text type. I've overridden the equal operator with lower(text) = lower(text). This won't work, you need to make a type instead. Actually I can do and have done this. It is being tested now. I did create an opclass. It creates a UNIQUE index just fine for the type using the lower() functionality. *If* it passes all of my testing I'll publish it tomorrow on general bits. Perhaps folks can help try to break it when I publish (or if you are around today Sunday, you can test in advance--email me directly.) If it doesn't pass the tests, well, I'm hoping it will be functionally close enough for common use. This implementation is all in SQL and plperl--no C code. Now my only complaint is that ORDER BY requires the USING op when it should recognize the information in the opclass for btree and for the type of the sort column. I can explain why it doesn't recognize the opclass information, but I think it should. If this is the way domains really are, I would strongly suggest expanding create domain to merge with create type (under) and allow us to list the basic functions. IMHO, the exact difference between a domain and a type is you get to choose your own definitions of the basic operations on a type. There's no free lunch: as soon as you start substituting operations the complexity involved goes up by an order of magnitude. Type inheritance for base types should work by inheriting all of the parent's definitions and overriding them only as necessary. What I'm trying to do is to demonstrate that we can weasle this functionality using domains. --elein [EMAIL PROTECTED] regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org OK. My article (and code) is published. It could probably use some more tries to find holes in the implementation. http://www.varlena.com/GeneralBits/128.php Known Problems and Issues: * Creating the table with an email PRIMARY KEY did not use our comparison function. It was necessary to create a unique index which explicitly used the email operator class. * ORDER BY requires USING op clause. * LIKE does not work. Use defined operator % instead. There are convincing arguments for and against this behavior. Feel free to argue one way or the other. [EMAIL PROTECTED] I've always depended on the [QA] of strangers. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Domains and supporting functions
elein [EMAIL PROTECTED] writes: I've got a domain based on a text type. I've overridden the equal operator with lower(text) = lower(text). This won't work, you need to make a type instead. If this is the way domains really are, I would strongly suggest expanding create domain to merge with create type (under) and allow us to list the basic functions. IMHO, the exact difference between a domain and a type is you get to choose your own definitions of the basic operations on a type. There's no free lunch: as soon as you start substituting operations the complexity involved goes up by an order of magnitude. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
