--
-- 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,
	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;
select lname, email from aliases where email = 'BOZO@clowns.com';
select a.email, a2.lname from aliases a JOIN aliases a2 USING (email);
select a.email, a2.lname from aliases a JOIN aliases a2 ON (UPPER(a.email)::email = a2.email);

\echo Comparison Operators 
\echo (result should be comparison based on lower()). Casting is not required.
select 'bozo@clowns.com'::email < 'ET@galaxy.net';
select 'ET@galaxy.net'::email > 'bozo@clowns.com';
select email from aliases where email <= 'ET@galaxy.net';
select email from aliases where email >= 'ET@galaxy.net';


\echo LIKE 
\echo Must cast text values.
\echo LIKE and ~~ with constants-casted works
\echo select column like value: works
select lname, email from aliases where email like 'BOZO%'::text; 
select lname, email from aliases where email ~~ 'BOZO%'::text;
select lname, email from aliases where email like lname || '%';

\echo select column like constant like constant works
select lname, email from aliases where 'abc@def.com'::email like 'ABC%'::text;
select lname, email from aliases where 'abc@def.com'::email ~~ 'ABC%'::text;

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

\echo EXPECT NO ROWS: LIKE and ~~ with unknown Right type constants dont work
select lname, email from aliases where email like 'BOZO%';
select lname, email from aliases where email ~~ 'BOZO%';


\echo 
\echo ORDER BY:
\echo Have to add using clause to tell which sort operator to use
\echo ASC: order by uses text instead of email comparison
select * from aliases order by email;
select * from aliases order by email ASC;
\echo DESC: order by uses text instead of email comparison
select * from aliases order by email DESC;
\echo Using USING < > works correctly
select * from aliases order by email using <;
select * from aliases order by email using >;
\echo