table

    drop table pfmaindetail;
    drop table pfstklist;
    drop table pfmflist;



CREATE TABLE pfmaindetail (
    pfPortfolioid 		varchar(50) CONSTRAINT porfolioid PRIMARY KEY, --> use userid+running number
	pfuserid 			varchar(40) NOT NULL,
    pfPortfolioname		varchar(100) NOT NULL,
    pfPurpose			varchar(600),
    pfBeneUsers 		varchar(40),
    pfStartDt			date NOT NULL,
    pfTargetDt			date NOT NULL,
    pfTargetIntRate 	numeric(5,2) NOT NULL,
    pfPlannedInvAmt 	numeric(16,5) NOT NULL,
    pfInvAmtFeq         varchar(10),
    pfStkAmtsplittype   varchar(10),
    pfmfAmtsplittype    varchar(10),    
    pfoctime			timestamp NOT NULL,
    pflmtime			timestamp NOT NULL
    );
	
  CREATE TABLE pfstklist (
    pfstklistid			varchar(50) CONSTRAINT stporfolioid PRIMARY KEY,  --> use date+running number for that date
    pfPortfolioid 		varchar(50) REFERENCES pfmaindetail(pfPortfolioid),
    pfstExchange		varchar(7) NOT NULL,
    pfstTradingsymbl	varchar(15) NOT NULL,
    pfstAmt             numeric(16,5),
    pfstPercent         numeric(5,2),
    pfstAllotedAmt 		numeric(16,5) NOT NULL,
    pfstTotUnit			numeric(16,0) NOT NULL,
	pfstoctime			timestamp NOT NULL,
    pfstlmtime			timestamp NOT NULL
    );
	
    CREATE TABLE pfmflist (
    pfmflistid			varchar(50)	CONSTRAINT mfporfolioid PRIMARY KEY, --> use date+running number for that date
    pfPortfolioid 		varchar(50) REFERENCES pfmaindetail(pfPortfolioid),	
    pfmfFundname		varchar(100) NOT NULL,
    pfmfAmt             numeric(16,5),
    pfmfPercent         numeric(5,2),
    pfmfAllotedAmt 		numeric(16,5) NOT NULL,
	pfmfoctime			timestamp NOT NULL,
    pfmflmtime			timestamp NOT NULL
    );
    

    
    INSERT INTO pfmaindetail
(pfPortfolioid,pfuserid,pfPortfolioname,pfPurpose,pfBeneUsers,pfStartDt,pfTargetDt,pfTargetIntRate,pfPlannedInvAmt,pfInvAmtFeq,pfStkAmtsplittype,pfmfAmtsplittype,pfoctime,pflmtime)
VALUES
(1,'AB001','NATRAYAN RETIRMENT','To Save for Retairment','','31-oct-2017','30-nov-2017',8.5,10000,'Daily','Amount','%','31-oct-2017','31-oct-2017'),
(2,'AB001','ANANTHI RETIRMENT','To Save for ANA Retairment','','15-nov-2017','30-nov-2020',9.5,1200,'Mothly','%','Amount','31-oct-2017','31-oct-2017')
;

INSERT INTO pfstklist
(pfstklistid,pfPortfolioid,pfstExchange,pfstTradingsymbl,pfstAmt,pfstPercent,pfstAllotedAmt,pfstTotUnit,pfstoctime,pfstlmtime)
VALUES
(1,1,'NSE','ITC',3000,0,3000,20,'31-oct-2017','31-oct-2017'),
(2,1,'NSE','SBI',3500,0,3500,10,'31-oct-2017','31-oct-2017'),
(3,2,'NSE','TATA',0,40,480,1,'31-oct-2017','31-oct-2017');

INSERT INTO pfmflist
(pfmflistid,pfPortfolioid,pfmfFundname,pfmfAmt,pfmfPercent,pfmfAllotedAmt,pfmfoctime,pfmflmtime)
VALUES
(1,1,'BIRLA MNC',0,20,2000,'31-oct-2017','31-oct-2017'),
(2,1,'ICICI PRU',0,15,1500,'31-oct-2017','31-oct-2017'),
(3,2,'QUANTUM EQU',720,0,720,'31-oct-2017','31-oct-2017');


#To get back the value as JSON format
select json_agg(art)
from (
	select *,
    	(select json_agg(b)
		from (
			select * 
            from pfstklist where pfportfolioid = a.pfportfolioid ) as b
         ) as pfstklist,
        	(select json_agg(c)
		from (
			select * 
            from pfmflist where pfportfolioid = a.pfportfolioid ) as c
         ) as pfmflist
	from pfmaindetail as a) art