--
-- Test create type email under text;
--
-- Elein Mustain, elein@varlena.com, http://www.varlena.com
-- Published 19-Feb-2006 at http://www.varlena.com/GeneralBits/128.php
-- License: BSD, Share and enjoy
--

drop table aliases;
Create table aliases (
	email	email UNIQUE PRIMARY KEY, -- broken: use unique index with btree (email email_ops)
	lname	text
);
create unique index aliases_email on aliases using btree (email email_ops);

\echo [Expect check_email ERROR]
insert into aliases values ('--bad@email.com', 'Bad');
\echo
\echo [Expect PK ERROR]
insert into aliases values ('pk@email.com', 'PK');
insert into aliases values ('PK@email.com', 'PK');
\echo
\echo [No errors]
insert into aliases values ('bozo@Clowns.com', 'Boz');
insert into aliases values ('Abalone@little.fishies.com', 'Abe');
insert into aliases values ('Carolina@my.mind.edu', 'Carolina');
insert into aliases values ('DDuck@ducks.disney.com', 'Donald');
insert into aliases values ('et@galaxy.net', 'ET');
insert into aliases values ('frodo@TheShire.net', 'Frodo');
insert into aliases values ('galahad@camelot.org', 'Sir');

-- 
\echo [Accessor functions]
select email, email_login(email) from aliases limit 2;
select email, email_fdomain(email) from aliases limit 2;
select email, email_domain_type(email) from aliases limit 2;
select email, email_reverse_domain(email) from aliases limit 2;
\echo

\echo [Equality tests]
select 'ABC@DEF.com'::email = 'abc@def.com'::email; --works
select lname, email from aliases where email = 'BOZO@clowns.com'; -- works 
select a.email, a2.lname from aliases a JOIN aliases a2 USING (email); --works
select a.email, a2.lname from aliases a JOIN aliases a2 ON (UPPER(a.email)::email = a2.email); --works

\echo [Comparison Operators]
\echo [result should be comparison based on lower()] 
\echo [one sided Casting is required for literals.]
select 'bozo@clowns.com'::email < 'ET@galaxy.net'; -- works 
select 'ET@galaxy.net'::email > 'bozo@clowns.com'; -- works 
select email from aliases where email <= 'ET@galaxy.net'; -- works 
select email from aliases where email >= 'ET@galaxy.net'; -- works 


\echo [LIKE  and ~~ with typed right operand]
\echo select column like value: works
select lname, email from aliases where email like 'BOZO%'::text;  --works
select lname, email from aliases where email ~~ 'BOZO%'::text; --works
select lname, email from aliases where email like lname || '%'; --works 

\echo  [direct function all to email_like works without casts]
select lname, email from aliases where email_like(email, 'BOZO%'); --works
select lname, email from aliases where email_like(email, lname || '%'); --works

-- these now work because email is a child of text.
\echo [Patch Corrected Cases]
\echo [LIKE and ~~ with unknown Right operand untyped work with patch]
select 'abc@def.com'::email like 'ABC%'; --now works-- broken: uses text
select lname, email from aliases where email like 'BOZO%'; --now works -- broken 
select lname, email from aliases where email ~~ 'BOZO%'; --now works -- broken 


\echo 
\echo [ORDER BY]
\echo [Have to add using clause to tell which sort operator to use]
\echo [ASC/DESC: dont change things]
select * from aliases order by email; --broken 
\echo Using USING < > works correctly
select * from aliases order by email using <;
select * from aliases order by email using >;
\echo

\echo 
\echo MISC Tests
select email ~~ upper(email) from aliases; -- now works uses email ~~
select email || email from aliases;  -- works uses text ||
select email_n(email,2) from aliases; -- works
select email * 2 from aliases; -- works: uses email_n