my nested if condition here doesn't work. don't know why. i'm new in postgresql. can me out of ? don't know do. :'(
create or replace function pl_uploadhousehoold(mchholdnumber character varying, mcbrgycode character varying, mcstio character varying, mcstreet character varying, mnhousenumber character varying, mclat character varying, mclong character varying, mcrespondentname character varying, mddateinterview date, mdstattime character varying, mdendtime character varying, mcpurok character varying, mnuserid integer, mczone character varying, mccategory character varying, mfile_path_household character varying, mfile_path character varying, mfile_respondent character varying, mdyear date, mnuser integer, mdaccomplished character varying, mdregistered character varying, mdvalidated character varying, mcsubcategory character varying, mcfacilityname character varying, mcposition character varying) returns setof tbl_household $body$ declare t_10 time without time zone; t_11 time without time zone; t_21 timestamp without time zone; t_22 timestamp without time zone; t_23 timestamp without time zone; hh_id character varying; begin if hh_id null if mdstattime !='' t_10:=(select cast (mdstattime time without time zone) dstattime); insert tbl_household(chholdnumber,cbrgycode,csitio,cstreet,nhousenumber,clat,clong,crespondentname,ddateinterview,dstattime,cpurok,nuserid,czone,ccategory,file_path_household, file_path, file_respondent,dyear,nuser, csubcategory, cfacilityname, cposition) values($1,$2,$3,$4,$5,$6,$7,$8,$9,t_10,$12,$13,$14,$15,$16,$17,$18,$19,$20,$24,$25,$26); end if; if mdendtime !='' t_11:=(select cast (mdendtime time without time zone) dendtime); insert tbl_household(chholdnumber,cbrgycode,csitio,cstreet,nhousenumber,clat,clong,crespondentname,ddateinterview,dendtime,cpurok,nuserid,czone,ccategory,file_path_household, file_path, file_respondent,dyear,nuser, csubcategory, cfacilityname, cposition) values($1,$2,$3,$4,$5,$6,$7,$8,$9,t_11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$24,$25,$26); end if; if mdaccomplished !='' t_21:=(select cast (mdaccomplished timestamp without time zone) daccomplished); insert tbl_household(chholdnumber,cbrgycode,csitio,cstreet,nhousenumber,clat,clong,crespondentname,ddateinterview,cpurok,nuserid,czone,ccategory,file_path_household, file_path, file_respondent,dyear,nuser,daccomplished, csubcategory, cfacilityname, cposition) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$12,$13,$14,$15,$16,$17,$18,$19,$20,t_21,$24,$25,$26); end if; if mdregistered !='' t_22:=(select cast (mdregistered timestamp without time zone) dregistered); insert tbl_household(chholdnumber,cbrgycode,csitio,cstreet,nhousenumber,clat,clong,crespondentname,ddateinterview,cpurok,nuserid,czone,ccategory,file_path_household, file_path, file_respondent,dyear,nuser,dregistered, csubcategory, cfacilityname, cposition) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$12,$13,$14,$15,$16,$17,$18,$19,$20,t_22,$24,$25,$26); end if; if mdvalidated !='' t_23:=(select cast (mdvalidated timestamp without time zone) dvalidated); insert tbl_household(chholdnumber,cbrgycode,csitio,cstreet,nhousenumber,clat,clong,crespondentname,ddateinterview,cpurok,nuserid,czone,ccategory,file_path_household, file_path, file_respondent,dyear,nuser,dvalidated, csubcategory, cfacilityname, cposition) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$12,$13,$14,$15,$16,$17,$18,$19,$20,t_23,$24,$25,$26); end if; if mdaccomplished ='' , mdstattime='' , mdendtime='' , mdregistered ='' , mdvalidated ='' insert tbl_household(chholdnumber,cbrgycode,csitio,cstreet,nhousenumber,clat,clong,crespondentname,ddateinterview,cpurok,nuserid,czone,ccategory,file_path_household, file_path, file_respondent,dyear,nuser, csubcategory, cfacilityname, cposition) values($1,$2,$3,$4,$5,$6,$7,$8,$9,$12,$13,$14,$15,$16,$17,$18,$19,$20,$24,$25,$26); end if; if mdaccomplished !='' , mdstattime!='' , mdendtime!='' , mdregistered !='' , mdvalidated !='' t_22:=(select cast (mdregistered timestamp without time zone) dregistered); t_21:=(select cast (mdaccomplished timestamp without time zone) daccomplished); t_10:=(select cast (mdstattime time without time zone) dstattime); t_11:=(select cast (mdendtime time without time zone) dendtime); t_23:=(select cast (mdvalidated timestamp without time zone) dvalidated); insert tbl_household(chholdnumber,cbrgycode,csitio,cstreet,nhousenumber,clat,clong,crespondentname,ddateinterview,dstattime,dendtime,cpurok,nuserid,czone,ccategory,file_path_household, file_path, file_respondent,dyear,nuser,daccomplished,dregistered,dvalidated, csubcategory, cfacilityname, cposition) values($1,$2,$3,$4,$5,$6,$7,$8,$9,t_10,t_11,$12,$13,$14,$15,$16,$17,$18,$19,$20,t_21,t_22,t_23,$24,$25,$26); end if; else end if; i've tested mdendtime null values. condition doesn't work.
you should remove last else statement @ bottom of function.
furthermore, in function using complicated way convert character string time or timestamp value, none of necessary. instead of doing:
if mdendtime != '' t_11 := (select cast (mdendtime time without time zone) dendtime); insert tbl_household(chholdnumber, dendtime, ...) values ($1, t_11, ...); end if; you can do:
insert tbl_household(chholdnumber, dendtime) values ($1, nullif(mdendtime, '')::time); but frank heikens said, function way complex. may end doing 6 inserts of same data. apparently doing household surveys in philippines , want store information on location of household , survey data. household , survey 2 separate entities , in proper database design entities have own table.
so start putting household data in table households. when have new survey, check if household data in table. if so, retrieve hholdid, otherwise insert data in households table returning hholdid.
now make table survey uses hholdid households table foreign key. in survey table put columns relevant information, including hholdid, such date of survey, start time, end time, etc. , can insert survey information there, in single row, without need function.
depending on survey data means might want repeat process of breaking of data entities. respondent information looks candidate: single respondent multiple surveys, make respondent table respid primary key , store value in survey table.
this analysis of "universe-of-discourse" @ heart of database modeling.
Comments
Post a Comment