-- === Views ===

--
-- All Answers as text
--

create or replace view av_v as
select orid, qid, avid, atype, ncols, 
    to_char(avalue,'FM999999999D99') as avalue 
from answer_numeric
UNION
   select orid, qid, avid, atype, ncols, 
      to_char(avalue,'dd-mon-yyyy') as avalue 
   from answer_date
UNION
   select orid, qid, avid, atype, ncols,
      (select case when avalue is null then NULL
       else (select case when avalue then 't' else 'f' end) end) as avalue
   from answer_boolean
UNION
   select orid, qid, avid, atype, ncols, 
      to_char(avalue,'999999D99') as avalue 
   from answer_float
UNION
   select orid, qid, avid, atype, ncols, avalue from answer_text
UNION
   select orid, qid, avid, atype, ncols,
      textin( record_out(
         ROW( to_char(astart,'dd-mon-yyyy'), to_char(aend,'dd-mon-yyyy')),0::oid ) )       
      as avalue
   from answer_date_2
UNION
   select orid, qid, avid, atype, ncols,
      textin(record_out(          
         ROW( to_char(astart,'FM999999999D009'), to_char(aend,'FM999999999D009') ),0::oid))
      as avalue
   from answer_numeric_2
UNION
   select orid, qid, avid, atype, ncols,
      textin(record_out(          
          ROW( addr,city,state,zip ),0::oid))
      as avalue
   from answer_addr;

--
-- Questions and Answers as Text
--
create or replace view qna as
select q.qid, q.question,  av_v.atype, av_v.ncols, av_v.avalue
from av_v JOIN questions q USING (qid);