--	Function: delete_location.sql
--      note that this deletes entries from some tables that do not
--      have foreign keys that mandate deletion, but that have location
--      data that should be deleted as part of a delete location function
--

CREATE OR REPLACE FUNCTION delete_location (varchar(8))
RETURNS INT  AS '
        DECLARE postlid ALIAS FOR $1;
                iret_code INT;
BEGIN
  	--
  	-- Delete Hgstation records
        -- Hgstation has fk referencing RiverStat
  	--
        DELETE FROM  Hgstation       WHERE lid = postlid;

  	--
  	-- Delete riverstation records
  	--
  	iret_code := delete_riverstat(postlid);

        --
        -- Delete reservoir related entries
        --
	iret_code :=delete_resvr(postlid);

	--
        -- Delete qc related records
        --
        DELETE FROM  IngestFilter    WHERE lid = postlid;
 	DELETE FROM  LocDataLimits   WHERE lid = postlid;
	DELETE FROM  RejectedData    WHERE lid = postlid;
	DELETE FROM  AlertAlarmVal   WHERE lid = postlid;
	DELETE FROM  AdjustFactor    WHERE lid = postlid;

	--
	-- Delete location related entries
	--
        DELETE FROM  Contacts        WHERE lid = postlid;
        DELETE FROM  Dcp             WHERE lid = postlid;
        DELETE FROM  Observer        WHERE lid = postlid;
        DELETE FROM  Telem           WHERE lid = postlid;
 
	DELETE FROM  ContingencyValue WHERE lid = postlid;
        DELETE FROM  FcstDischarge   WHERE lid = postlid;
        DELETE FROM  FcstHeight      WHERE lid = postlid;
        DELETE FROM  FcstPrecip      WHERE lid = postlid;
        DELETE FROM  FcstTemperature WHERE lid = postlid;
        DELETE FROM  FcstOther       WHERE lid = postlid;
 	DELETE FROM  ProcValue       WHERE lid = postlid;
 	DELETE FROM  CommentValue    WHERE lid = postlid;
  	DELETE FROM  PairedValue     WHERE lid = postlid;
	DELETE FROM  UnkStnValue     WHERE lid = postlid;
 	DELETE FROM  UnkStn          WHERE lid = postlid;

        DELETE FROM  LatestObsValue  WHERE lid = postlid;
        DELETE FROM  RiverStatus     WHERE lid = postlid;

        DELETE FROM  Agricultural    WHERE lid = postlid;
        DELETE FROM  Discharge       WHERE lid = postlid;
        DELETE FROM  Evaporation     WHERE lid = postlid;
        DELETE FROM  FishCount       WHERE lid = postlid;
        DELETE FROM  FloodTs         WHERE lid = postlid;
	DELETE FROM  GateDam         WHERE lid = postlid;
        DELETE FROM  Ground          WHERE lid = postlid;
        DELETE FROM  Height          WHERE lid = postlid;
	DELETE FROM  Ice             WHERE lid = postlid;
        DELETE FROM  Lake            WHERE lid = postlid;
        DELETE FROM  Moisture        WHERE lid = postlid;
        DELETE FROM  Power           WHERE lid = postlid;
	DELETE FROM  Pressure        WHERE lid = postlid;
	DELETE FROM  Radiation       WHERE lid = postlid;
        DELETE FROM  Snow            WHERE lid = postlid;
        DELETE FROM  Temperature     WHERE lid = postlid;
        DELETE FROM  WaterQuality    WHERE lid = postlid;
        DELETE FROM  Weather         WHERE lid = postlid;
        DELETE FROM  Wind            WHERE lid = postlid;
        DELETE FROM  YUnique         WHERE lid = postlid;
 
        DELETE FROM  ArealFcst       WHERE lid = postlid;
        DELETE FROM  ArealObs        WHERE lid = postlid;

        DELETE FROM  MonthlyValues   WHERE lid = postlid;

        DELETE FROM  CurPC           WHERE lid = postlid;
        DELETE FROM  CurPP           WHERE lid = postlid; 
        DELETE FROM  HourlyPC        WHERE lid = postlid;
        DELETE FROM  HourlyPP        WHERE lid = postlid;
        DELETE FROM  RawPC           WHERE lid = postlid;
        DELETE FROM  RawPP           WHERE lid = postlid;
        DELETE FROM  RawPother       WHERE lid = postlid;

        DELETE FROM  LocExtAgency    WHERE lid = postlid;
	DELETE FROM  LocImage        WHERE lid = postlid;
        DELETE FROM  OFSStnTrans     WHERE lid = postlid;
	DELETE FROM  ProductLink     WHERE lid = postlid;
 
 	DELETE FROM  StnClass 	     WHERE lid = postlid;

        DELETE FROM  Location        WHERE lid = postlid;
 
RETURN 0;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
--
--	proc_delete_radar.sql
--	Supports a cascading delete when
--	a row is removed from the RadarLoc table.
--

CREATE OR REPLACE FUNCTION delete_radar (char(3)) RETURNS INT  AS '
DECLARE delradid ALIAS FOR $1;
BEGIN
	DELETE FROM   DpaAdapt		  WHERE radid = delradid;
	DELETE FROM   RWRadarResult       WHERE radid = delradid;
	DELETE FROM   RWBiasDyn           WHERE radid = delradid;
	DELETE FROM   DPARadar            WHERE radid = delradid;
	DELETE FROM   RadarLoc            WHERE radid = delradid;
RETURN 0;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
--
--	delete_resvr.sql
--	Supports a cascading delete when
--	a row is removed from the reservoir table.
--

CREATE OR REPLACE FUNCTION delete_resvr (varchar(8))
RETURNS INT  AS '
DECLARE postlid ALIAS FOR $1;
BEGIN
	DELETE FROM rescap    WHERE lid = postlid;
	DELETE FROM reservoir WHERE lid = postlid;
RETURN 0;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------

--	delete_riverstat.sql
--	Supports a cascading delete when
--	a row is removed from the riverstat table.
--

CREATE OR REPLACE FUNCTION delete_riverstat (varchar(8))
RETURNS INT  AS '
DECLARE postlid ALIAS FOR $1;
BEGIN
        -- 
        -- Delete riverstation related records
        -- 
        DELETE FROM  Benchmark    WHERE lid = postlid; 
        DELETE FROM  Countynum    WHERE lid = postlid; 
        DELETE FROM  Crest        WHERE lid = postlid; 
        DELETE FROM  Datum        WHERE lid = postlid; 
        DELETE FROM  Descrip      WHERE lid = postlid; 
	DELETE FROM  Flood        WHERE lid = postlid; 
        DELETE FROM  Floodcat     WHERE lid = postlid; 
        DELETE FROM  Floodstmt    WHERE lid = postlid; 
        DELETE FROM  FpPrevProd   WHERE lid = postlid; 
        DELETE FROM  Gage         WHERE lid = postlid; 
        DELETE FROM  Lowwater     WHERE lid = postlid; 
        DELETE FROM  Pub          WHERE lid = postlid; 
        DELETE FROM  Rating       WHERE lid = postlid; 
        DELETE FROM  RatingShift  WHERE lid = postlid; 
        DELETE FROM  Refer        WHERE lid = postlid; 
        DELETE FROM  RpfFcstPoint WHERE lid = postlid;
        DELETE FROM  SSHPConfig   WHERE lid = postlid;
        DELETE FROM  UnitGraph    WHERE lid = postlid;
	DELETE FROM  Zonenum      WHERE lid = postlid; 
        DELETE FROM  Riverstat    WHERE lid = postlid; 
RETURN 0;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
--
--     fcst_pe()
--     Post the data to the appropriate pe-based table.
--     Modified for postgresql Sep 28, 2004
--

CREATE OR REPLACE FUNCTION fcst_pe
	(
         varchar(8),
         varchar(2),
	 integer,
	 varchar(2),
	 varchar(1),
	 float4,
         timestamp,
         timestamp,	
         float8,
         varchar(1),
         integer,
         integer,
         varchar(10),
         timestamp,
         timestamp,
         integer
	)
	
RETURNS INT  AS '
        DECLARE fcst_record     fcstheight%ROWTYPE;
		newlid          ALIAS FOR   $1;	
		newpe           ALIAS FOR   $2;
		newdur          ALIAS FOR   $3;
		newts           ALIAS FOR   $4;
		newextr         ALIAS FOR   $5;
		newprob         ALIAS FOR   $6;
		newvalidtime    ALIAS FOR   $7;
		newbasistime    ALIAS FOR   $8;
		newvalue        ALIAS FOR   $9;
		newrawqual      ALIAS FOR   $10;
		newqc           ALIAS FOR   $11;
		newrev	        ALIAS FOR   $12;
		newprid         ALIAS FOR   $13;
		newprodtime     ALIAS FOR   $14;
		newposttime     ALIAS FOR   $15;
		do_update       ALIAS FOR   $16;
                conf_update     integer;

BEGIN
    conf_update := 0;
------------------------------------------------------
--Post forecast height data
--
  if newpe like ''H%'' then
     SELECT INTO fcst_record * FROM FcstHeight
     WHERE lid = newlid
     AND   pe = newpe
     AND   dur = newdur
     AND   ts = newts
     AND   extremum = newextr
     AND   probability  = newprob
     AND   basistime = newbasistime
     AND   validtime = newvalidtime;

     if FOUND then
        if fcst_record.value = -9999. then
           conf_update := 1;
        else
           if do_update = 2 then
              if fcst_record.value <> newvalue then
                 conf_update := 1;
              end if;
           else
              if do_update = 1 then
                 conf_update := 1;
              end if;
           end if;
        end if;
        
        if conf_update = 1 then
           UPDATE FcstHeight SET
	   value          = newvalue,
	   shef_qual_code = newrawqual,
	   quality_code   = newqc,
	   revision       = newrev,
	   product_id     = newprid,
	   producttime    = newprodtime,
	   postingtime    = newposttime
	   WHERE lid = newlid and pe  = newpe and
	   dur = newdur and ts  = newts and
	   extremum     = newextr and
	   probability  = newprob and
	   validtime    = newvalidtime and
	   basistime    = newbasistime;
        end if;
     else
	INSERT INTO FcstHeight
	VALUES(newlid, newpe, newdur, newts, newextr, newprob, newvalidtime,
        newbasistime, newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
        newposttime);				
     end if;

--
--Process forecast precip data
--
  elsif newpe like ''P%'' then
        SELECT INTO fcst_record * FROM FcstPrecip 
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   probability  = newprob
        AND   basistime = newbasistime
        AND   validtime = newvalidtime;

        if FOUND then
           if fcst_record.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if fcst_record.value <> newvalue then 
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE FcstPrecip SET 
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe  = newpe and
              dur = newdur and ts  = newts and
              extremum     = newextr and
              probability  = newprob and
              validtime    = newvalidtime and
              basistime    = newbasistime;
           end if;
        else
           INSERT INTO FcstPrecip
           VALUES( newlid, newpe, newdur, newts, newextr, newprob, newvalidtime,
           newbasistime,newvalue, newrawqual, newqc, newrev,newprid,newprodtime,
           newposttime);
        end if;

--
--Process forecast discharge data
--
  elsif newpe like ''Q%'' then
        SELECT INTO fcst_record * FROM FcstDischarge
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   probability  = newprob
        AND   basistime = newbasistime
        AND   validtime = newvalidtime;

        if FOUND then
           if fcst_record.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if fcst_record.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;
           
           if conf_update = 1 then
              UPDATE FcstDischarge SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe  = newpe and
              dur = newdur and ts  = newts and
              extremum     = newextr and
              probability  = newprob and
              validtime    = newvalidtime and
              basistime    = newbasistime;
           end if;
        else
           INSERT INTO FcstDischarge
           VALUES( newlid, newpe, newdur, newts, newextr, newprob, newvalidtime,
           newbasistime,newvalue,newrawqual, newqc, newrev, newprid,newprodtime,
           newposttime);
        end if;

--
--Process forecast temperature amounts
--
  elsif newpe like ''T%'' then
        SELECT INTO fcst_record * FROM FcstTemperature
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   probability  = newprob
        AND   basistime = newbasistime
        AND   validtime = newvalidtime;

        if FOUND then
           if fcst_record.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if fcst_record.value <> newvalue then 
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE FcstTemperature SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe  = newpe and
              dur = newdur and ts  = newts and
              extremum     = newextr and
              probability  = newprob and
              validtime    = newvalidtime and
              basistime    = newbasistime;
           end if;
        else
           INSERT INTO FcstTemperature
           VALUES( newlid, newpe, newdur, newts, newextr, newprob, newvalidtime,
           newbasistime,newvalue,newrawqual,newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process forecast other data    
--
  else         
       SELECT INTO fcst_record * FROM FcstOther
       WHERE lid = newlid
       AND   pe = newpe
       AND   dur = newdur
       AND   ts = newts
       AND   extremum = newextr
       AND   probability  = newprob
       AND   basistime = newbasistime
       AND   validtime = newvalidtime;

       if FOUND then
          if fcst_record.value = -9999. then
             conf_update := 1;
          else
             if do_update = 2 then
                if fcst_record.value <> newvalue then 
                   conf_update := 1;
                end if;
             else
                if do_update = 1 then
                   conf_update := 1;
                end if;
             end if;
          end if;

          if conf_update = 1 then
             UPDATE FcstOther SET
             value          = newvalue,
             shef_qual_code = newrawqual,
             quality_code   = newqc,
             revision       = newrev,
             product_id     = newprid,
             producttime    = newprodtime,
             postingtime    = newposttime
             WHERE lid = newlid and pe  = newpe and
             dur = newdur and ts  = newts and
             extremum     = newextr and
             probability  = newprob and
             validtime    = newvalidtime and
             basistime    = newbasistime;
          end if;
       else
          INSERT INTO FcstOther
          VALUES( newlid, newpe, newdur, newts, newextr, newprob, newvalidtime,
          newbasistime,newvalue,newrawqual,newqc, newrev, newprid, newprodtime,
          newposttime);
       end if;
  end if;

RETURN NULL;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
--
--	Function: load_obs_river.sql
--      Purpose:  Loads the latest valid observed data record for the
-- 	specified key into the RiverStatus table.
--      Modified for postgresql: Sep 27, 2004
--

CREATE OR REPLACE FUNCTION load_obs_river
	(
	 varchar(8),
	 varchar(2),
	 varchar(2)
	)
RETURNS INT     AS '
	--
	-- Local variables
	--
        DECLARE rstat        riverstatus%ROWTYPE;
                postlid      ALIAS FOR  $1;
                postpe       ALIAS FOR  $2;
                postts       ALIAS FOR  $3;

	        curdur       int;
	        curextr      char(1);
	        curtime	     timestamp;
	        curvalue     float = -9999.;
	        qc_threshold int = 1073741824;
                rstat_found  boolean;
BEGIN
	-- Determine if record already exists
        SELECT INTO rstat * FROM riverstatus
	WHERE lid = postlid and pe = postpe and ts = postts;
		
        rstat_found := FOUND;
		
	-- Get the latest valid value from the appropriate table
	-- note that for all the queries, only data that is instantaneous
	-- and having a null extremum is considered.  this constraint results
	-- from the fact that we are trying to find the latest obstime for 
	-- a given lid, pe, ts but the tables being read also have dur and 
	-- extremum as key fields.  this approach is considered ok since
	-- observed data is rarely ever of anything but null extremum and
	-- instantaneous duration.  a more robust alternative that uses the
	-- maximum value, regardless of duration or extremum, for the 
	-- latest time was considered, but not implemented since this
	-- would add more processing time just to consider a case
	-- that would very rarely, if ever, occur.
	
	if postpe like ''Q%'' then
	   SELECT INTO curtime max(obstime)
                FROM discharge
		WHERE lid = postlid and pe = postpe and ts = postts and 
		dur = 0 and extremum = ''Z'' and quality_code >= qc_threshold;	
	   SELECT INTO curdur, curextr, curvalue 
                dur, extremum, "value"
	   	FROM discharge
		WHERE lid = postlid and pe = postpe and ts = postts and
		dur = 0 and extremum = ''Z'' and quality_code >= qc_threshold and
		obstime = curtime;
        elsif postpe like ''H%'' then
	   SELECT INTO curtime max(obstime)
                FROM height
		WHERE lid = postlid and pe = postpe and ts = postts and 
		dur = 0 and extremum = ''Z'' and quality_code >= qc_threshold;
	   SELECT INTO curdur, curextr, curvalue
                dur, extremum, "value"
                FROM height
		WHERE lid = postlid and pe = postpe and ts = postts and 
		dur = 0 and extremum = ''Z'' and quality_code >= qc_threshold and
		obstime = curtime;
	end if;
			 
	-- If no value found, then delete any record that may be
	-- in the riverstatus table, which can occur when 
	-- this function is called by an application deleting the
	-- last item for the lid,pe,ts combination
	
	if rstat_found = FALSE AND curvalue IS NULL then
		RETURN 0;
	elsif rstat_found AND curvalue IS NULL then
	        DELETE FROM riverstatus
                   WHERE lid = postlid and pe = postpe and ts = postts;
	
	-- If entry exists already, then update it
	-- on updates, dont bother resetting basistime and probability
        elsif rstat_found then
                UPDATE riverstatus
                  SET   dur        = curdur,
		  	extremum   = curextr,
		        validtime  = curtime,
                        "value"      = curvalue
                  WHERE lid = postlid and pe = postpe and ts = postts;

	-- Insert new entry
	-- set probability to default and basistime to NULL
        else
                INSERT INTO RiverStatus
                VALUES
                (
                        postlid,   postpe,    curdur,
			postts,    curextr,   -1.,  
			curtime,   NULL,      curvalue
                );
        end if;
RETURN 0;
END;
'  LANGUAGE 'plpgsql';
-------------------------------------------------------------------------------
--
--	Procedure:	obs_pe.sql
-- 	Main procedure for posting to pe-based tables.
--      updated for PostgreSQL June  2004
--      Called by post_pe_data routine 
-- 

CREATE OR REPLACE FUNCTION obs_pe (
         varchar(8),
         varchar(2),
         integer,
         varchar(2),
         varchar(1),
         timestamp,            
         float8,
         varchar(1),
         integer,
         integer,
         varchar(10),
         timestamp,
         timestamp,
         integer
         )
RETURNS INT     AS '
DECLARE obs_rec       height%ROWTYPE;
        newlid        ALIAS FOR   $1;
        newpe         ALIAS FOR   $2;
        newdur        ALIAS FOR   $3;
        newts         ALIAS FOR   $4;
        newextr       ALIAS FOR   $5;
        newobstime    ALIAS FOR   $6;
        newvalue      ALIAS FOR   $7;
        newrawqual    ALIAS FOR   $8;
        newqc         ALIAS FOR   $9;
        newrev        ALIAS FOR   $10;
        newprid       ALIAS FOR   $11;
        newprodtime   ALIAS FOR   $12;
        newposttime   ALIAS FOR   $13;
        do_update     ALIAS FOR   $14;
        conf_update   integer;
BEGIN
    conf_update := 0;
------------------------------------------------------
-- Process the data based on the PE attribute
-- The more common PE are placed earliest in this if-elseif stack
-- The order is height (H), pressure (Pspecial), precip (Pother),
-- discharge (Q), snow (S), temperature (T), wind (U), then...
-- agricultural (A), evaporation (E), fishcount(F), ground (G),
-- ice (I), lake (L), moisture (M), gatedam (N), radiation (R),
-- power (V), waterquality (W), weather (X), yunique (Y).
------------------------------------------------------
--
--Process height data
--
  if newpe like ''H%'' then				

     SELECT INTO obs_rec * FROM height
     WHERE lid = newlid
     AND   pe = newpe
     AND   dur = newdur
     AND   ts = newts
     AND   extremum = newextr
     AND   obstime = newobstime;

     if FOUND then
        if obs_rec.value = -9999. then
           conf_update := 1;
        else
           if do_update = 2 then
              if obs_rec.value <> newvalue then
                 conf_update := 1;
              end if;
           else 
              if do_update = 1 then
                 conf_update := 1;
              end if;
           end if;
        end if;

        if conf_update = 1 then
           UPDATE height SET
           value          = newvalue,
           shef_qual_code = newrawqual,
           quality_code   = newqc,
           revision       = newrev,
           product_id     = newprid,
           producttime    = newprodtime,
           postingtime    = newposttime
           WHERE lid = newlid and pe = newpe and
           dur = newdur and ts = newts and
           extremum = newextr and obstime = newobstime;
         end if;

    else
       INSERT INTO height
       VALUES ( newlid, newpe, newdur, newts, newextr, newobstime, 
       newvalue, newrawqual, newqc, newrev, newprid, newprodtime, 
       newposttime);
    end if;
		   
--
--Process pressure data
--
  elsif newpe in (''PA'', ''PD'', ''PE'', ''PL'') then
        SELECT INTO obs_rec * FROM pressure
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else 
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE pressure SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
       else
           INSERT INTO pressure
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime, 
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime, 
           newposttime);
       end if;

--
--Process Discharge data
--
  elsif newpe like ''Q%'' then                
        SELECT INTO obs_rec * FROM discharge
	WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE discharge SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
       else
           INSERT INTO discharge
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
       end if;

--
--Process snow data
--
  elsif newpe like ''S%'' then
   
        SELECT INTO obs_rec * FROM snow
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE snow SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
       else
           INSERT INTO snow
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
       end if;
	
--
--Process temperature data
--
  elsif newpe like ''T%'' then                
   
        SELECT INTO obs_rec * FROM temperature
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1  then
              UPDATE temperature SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO temperature
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;
   
 --
 --Process wind data
 --
   elsif newpe like ''U%'' then                
   
        SELECT INTO obs_rec * FROM wind
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE wind SET
              value      = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO wind
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;
	
--
--Process agricultural data
--

  elsif newpe like ''A%'' then                
   
        SELECT INTO obs_rec * FROM agricultural
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE agricultural SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO agricultural
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;
   
 --
 --Process evaporation data
 --
   elsif newpe like ''E%'' then
   
         SELECT INTO obs_rec * FROM evaporation
         WHERE lid = newlid
         AND   pe = newpe
         AND   dur = newdur
         AND   ts = newts
         AND   extremum = newextr
         AND   obstime = newobstime;

         if FOUND then
            if obs_rec.value = -9999. then
               conf_update := 1;
            else
               if do_update = 2 then
                  if obs_rec.value <> newvalue then
                     conf_update := 1;
                  end if;
               else
                  if do_update = 1 then
                     conf_update := 1;
                  end if;
               end if;
            end if;

            if conf_update = 1 then
               UPDATE evaporation SET
               value          = newvalue,
               shef_qual_code = newrawqual,
               quality_code   = newqc,
               revision       = newrev,
               product_id     = newprid,
               producttime    = newprodtime,
               postingtime    = newposttime
               WHERE lid = newlid and pe = newpe and
               dur = newdur and ts = newts and
               extremum = newextr and obstime = newobstime;
            end if;
         else
            INSERT INTO evaporation
            VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
            newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
            newposttime);
         end if;

--
--Process fish count data
--
  elsif newpe like ''F%'' then
   
        SELECT INTO obs_rec * FROM fishcount
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE fishcount SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO fishcount
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process ground data
--
  elsif newpe like ''G%'' then                
   
        SELECT INTO obs_rec * FROM ground
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE ground SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO ground
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process Ice data
--
  elsif newpe like ''I%'' then                               
   
        SELECT INTO obs_rec * FROM ice
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE ice SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO ice
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;
		      
--
--Process lake data
--
  elsif newpe like ''L%'' then                                
   
        SELECT INTO obs_rec * FROM lake
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE lake SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO lake
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process moisture data
--
  elsif newpe like ''M%'' then
   
        SELECT INTO obs_rec * FROM moisture
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE moisture SET
              value      = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
          end if; 
        else
           INSERT INTO moisture
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process gate and dam data
--
  elsif newpe like ''N%'' then
   
        SELECT INTO obs_rec * FROM gatedam
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE gatedam SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if; 
        else
           INSERT INTO gatedam
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process radiation data
--
  elsif newpe like ''R%'' then                                
   
        SELECT INTO obs_rec * FROM radiation
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                  if do_update = 1 then
                     conf_update := 1;
                  end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE radiation SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO radiation
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process generation and generator data
--
  elsif newpe like ''V%'' then                
   
        SELECT INTO obs_rec * FROM power
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE power SET
              value      = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if; 
        else
           INSERT INTO power
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process water quality data
--
  elsif newpe like ''W%'' then		      
   
        SELECT INTO obs_rec * FROM waterquality
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE waterquality SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO waterquality
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process weather data
--
  elsif newpe like ''X%'' then		      
   
        SELECT INTO obs_rec * FROM weather
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE weather SET
              value      = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO weather
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

--
--Process Yunique data
--
  elsif newpe like ''Y%'' then                
   
        SELECT INTO obs_rec * FROM yunique
        WHERE lid = newlid
        AND   pe = newpe
        AND   dur = newdur
        AND   ts = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE yunique SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO yunique
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime,
           newvalue, newrawqual, newqc, newrev, newprid, newprodtime,
           newposttime);
        end if;

  end if;

RETURN 0;
END;
'  LANGUAGE 'plpgsql';
-------------------------------------------------------------------------------
--
--	Function:	obs_precip.sql
-- 	Main procedure for posting precip data to pe-based tables.
--      Modified for postgresql Sep 28, 2004
-- 

CREATE OR REPLACE FUNCTION obs_precip
	(
	 varchar(8),
	 varchar(2),
	 integer,
	 varchar(2),
         varchar(1),
         timestamp,
         float8,
         varchar(1),
         integer,
         integer,
         varchar(10),
         timestamp,
         timestamp,
         integer
	)
RETURNS INT     AS '
        DECLARE obs_precip_rec rawpp%ROWTYPE;
                newlid         ALIAS FOR   $1;
                newpe          ALIAS FOR   $2;
                newdur         ALIAS FOR   $3;
                newts          ALIAS FOR   $4;
                newextr        ALIAS FOR   $5;
                newobstime     ALIAS FOR   $6;
                newvalue       ALIAS FOR   $7;
                newrawqual     ALIAS FOR   $8;
                newqc          ALIAS FOR   $9;
                newrev         ALIAS FOR   $10;
                newprid        ALIAS FOR   $11;
                newprodtime    ALIAS FOR   $12;
                newposttime    ALIAS FOR   $13;
                do_update      ALIAS FOR   $14;
                conf_update    integer;

BEGIN
    conf_update := 0;

--Process the data based on the PE attribute
--Post data to the appropriate table as instructed
--      
	
--
--Post PC data
--
  if (newpe = ''PC'') then				
      SELECT INTO obs_precip_rec * FROM rawPC 
      WHERE lid = newlid
      AND   pe  = newpe
      AND   dur = newdur
      AND   ts  = newts
      AND   extremum = newextr
      AND   obstime = newobstime;

      if FOUND then
         if obs_precip_rec.value = -9999. then
            conf_update := 1;
         else
            if do_update = 2 then
               if obs_precip_rec.value <> newvalue then 
                  conf_update := 1;
               end if;
            else
               if do_update = 1 then
                  conf_update := 1;
               end if;
            end if;
         end if;

         if conf_update = 1 then
            UPDATE rawPC SET
	    value          = newvalue,
	    shef_qual_code = newrawqual,
	    quality_code   = newqc, 
	    revision       = newrev,
	    product_id     = newprid,
	    producttime    = newprodtime,
	    postingtime    = newposttime
	    WHERE lid = newlid and pe = newpe and
	    dur = newdur and ts = newts and
	    extremum = newextr and obstime = newobstime;
	 end if;

      else
         INSERT INTO rawPC
         VALUES ( newlid, newpe, newdur, newts, newextr, newobstime, newvalue, 
         newrawqual, newqc, newrev, newprid, newprodtime, newposttime);
      end if;

-- 
--Post PP data  
--
  elsif (newpe = ''PP'') then				
        SELECT INTO obs_precip_rec * FROM rawPP 
        WHERE lid = newlid
        AND   pe  = newpe
        AND   dur = newdur
        AND   ts  = newts
        AND   extremum = newextr
        AND   obstime = newobstime;

        if FOUND then
           if obs_precip_rec.value = -9999. then
              conf_update := 1;
           else
              if do_update = 2 then
                 if obs_precip_rec.value <> newvalue then
                    conf_update := 1;
                 end if;
              else
                 if do_update = 1 then
                    conf_update := 1;
                 end if;
              end if;
           end if;

           if conf_update = 1 then
              UPDATE rawPP SET
              value          = newvalue,
              shef_qual_code = newrawqual,
              quality_code   = newqc,
              revision       = newrev,
              product_id     = newprid,
              producttime    = newprodtime,
              postingtime    = newposttime
              WHERE lid = newlid and pe = newpe and
              dur = newdur and ts = newts and
              extremum = newextr and obstime = newobstime;
           end if;
        else
           INSERT INTO rawPP
           VALUES ( newlid, newpe, newdur, newts, newextr, newobstime, newvalue,
           newrawqual, newqc, newrev, newprid, newprodtime, newposttime);
        end if;

-- 
--Post precip data that is neither PC nor PP
--    
  else				
     SELECT INTO obs_precip_rec * FROM rawPother
     WHERE lid = newlid
     AND   pe  = newpe
     AND   dur = newdur
     AND   ts  = newts
     AND   extremum = newextr
     AND   obstime = newobstime;

     if FOUND then
        if obs_precip_rec.value = -9999. then
           conf_update := 1;
        else
           if do_update = 2 then
              if obs_precip_rec.value <> newvalue then
                 conf_update := 1;
              end if;
           else
              if do_update = 1 then
                 conf_update := 1;
              end if;
           end if;
        end if;

        if conf_update = 1 then
           UPDATE rawPother SET
           value          = newvalue,
           shef_qual_code = newrawqual,
           quality_code   = newqc,
           revision       = newrev,
           product_id     = newprid,
           producttime    = newprodtime,
           postingtime    = newposttime
           WHERE lid = newlid and pe = newpe and
           dur = newdur and ts = newts and
           extremum = newextr and obstime = newobstime;
        end if;

      else
        INSERT INTO rawPother
        VALUES ( newlid, newpe, newdur, newts, newextr, newobstime, newvalue,
        newrawqual, newqc, newrev, newprid, newprodtime, newposttime);
      end if;
  end if;
	   
RETURN 0;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION obs_rawpc_ins () RETURNS TRIGGER AS '
BEGIN
    INSERT INTO CurPC
    VALUES (
            NEW.lid,
            NEW.pe,
            NEW.dur,
            NEW.ts,
            NEW.extremum,
            NEW.obstime,
            NEW.value,
            NEW.shef_qual_code,
            NEW.quality_code,
            NEW.revision,
            NEW.product_id,
            NEW.producttime,
            NEW.postingtime
           );
RETURN NEW;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION obs_rawpc_upd () RETURNS TRIGGER AS '
DECLARE counter int;
BEGIN
    --Update the info in CurPC
      SELECT INTO counter count(*) FROM CurPC
      WHERE   lid      = NEW.lid
      and     pe       = NEW.pe
      and     dur      = NEW.dur
      and     ts       = NEW.ts
      and     extremum = NEW.extremum
      and     obstime  = NEW.obstime;

      if (counter > 0) then
          UPDATE CurPC
          SET   value          = NEW.value,
          shef_qual_code = NEW.shef_qual_code,
          quality_code   = NEW.quality_code,
          revision       = NEW.revision,
          product_id     = NEW.product_id,
          producttime    = NEW.producttime,
          postingtime    = NEW.postingtime
          WHERE lid      = NEW.lid
          and pe       = NEW.pe
          and dur      = NEW.dur
          and ts       = NEW.ts
          and extremum = NEW.extremum
          and obstime  = NEW.obstime;
      else
          INSERT INTO CurPC
          VALUES ( NEW.lid, NEW.pe, NEW.dur, NEW.ts, NEW.extremum, NEW.obstime,
          NEW.value, NEW.shef_qual_code, NEW.quality_code, NEW.revision,
          NEW.product_id, NEW.producttime, NEW.postingtime);

          DELETE FROM CurPC
          WHERE lid      = OLD.lid
          AND   pe       = OLD.pe
          AND   dur      = OLD.dur
          AND   ts       = OLD.ts
          AND   extremum = OLD.extremum
          AND   obstime  = OLD.obstime;
      end if;

RETURN NEW;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION obs_rawpp_ins () RETURNS TRIGGER AS '
BEGIN
    INSERT INTO CurPP
    VALUES ( NEW.lid, NEW.pe, NEW.dur, NEW.ts, NEW.extremum, NEW.obstime,
    NEW.value, NEW.shef_qual_code, NEW.quality_code, NEW.revision, 
    NEW.product_id, NEW.producttime, NEW.postingtime);
RETURN NEW;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION obs_rawpp_upd () RETURNS TRIGGER AS '
DECLARE counter int;
BEGIN
    --Update the info in CurPP
    SELECT INTO counter count(*) FROM CurPP
    WHERE   lid      = NEW.lid
    and     pe       = NEW.pe
    and     dur      = NEW.dur
    and     ts       = NEW.ts
    and     extremum = NEW.extremum
    and     obstime  = NEW.obstime;

    if (counter > 0) then
       UPDATE CurPP
       SET   value          = NEW.value,
       shef_qual_code = NEW.shef_qual_code,
       quality_code   = NEW.quality_code,
       revision       = NEW.revision,
       product_id     = NEW.product_id,
       producttime    = NEW.producttime,
       postingtime    = NEW.postingtime
       WHERE lid      = NEW.lid
       and pe         = NEW.pe
       and dur        = NEW.dur
       and ts         = NEW.ts
       and extremum   = NEW.extremum
       and obstime    = NEW.obstime;
    else
       INSERT INTO CurPP
       VALUES ( NEW.lid, NEW.pe, NEW.dur, NEW.ts, NEW.extremum, NEW.obstime,
       NEW.value, NEW.shef_qual_code, NEW.quality_code, NEW.revision,
       NEW.product_id, NEW.producttime, NEW.postingtime);

       DELETE FROM CurPP
       WHERE lid      = OLD.lid
       AND   pe       = OLD.pe
       AND   dur      = OLD.dur
       AND   ts       = OLD.ts
       AND   extremum = OLD.extremum
       AND   obstime  = OLD.obstime;
    end if;

RETURN NEW;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION obs_river_ins () RETURNS TRIGGER AS '
DECLARE river RiverStatus%ROWTYPE;
BEGIN
    --
    -- dont insert data that is definitely bad note the hardcoded number.
	
    if (NEW.quality_code < 1073741824) then
        RETURN NEW;
    end if;
	 
    SELECT INTO river * FROM RiverStatus
    WHERE lid = NEW.lid
    AND   pe = NEW.pe
    AND   ts = NEW.ts;

    if FOUND then
	-- need to check for NULL obstime or the logical check will fail
	-- if updating, dont bother with null probability or basistime fields
		 	
	UPDATE RiverStatus 
	SET   dur = NEW.dur,
	extremum  = NEW.extremum,
	validtime = NEW.obstime,
	value     = NEW.value		
	WHERE lid = NEW.lid and pe = NEW.pe
	and ts = NEW.ts and (validtime < NEW.obstime or validtime IS NULL);
    else		
	INSERT INTO RiverStatus
	VALUES ( NEW.lid, NEW.pe, NEW.dur, NEW.ts, NEW.extremum, -1., 
	NEW.obstime, NULL, NEW.value);            
    end if;
				    
RETURN NEW;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
--
--	obs_river_upd.sql
--	This function updates the latest observed value in the
--      RiverStatus table for the given location.
--      Only do the update if the value or its time have changed.
--

CREATE OR REPLACE FUNCTION obs_river_upd () RETURNS TRIGGER AS '
DECLARE
    iret_code INT;
BEGIN
    if (NEW.value = OLD.value AND NEW.obstime = OLD.obstime) then 
        RETURN NEW;
    end if;
    iret_code := load_obs_river (NEW.lid, NEW.pe, NEW.ts);
RETURN NEW;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------
--
--	Procedure:	proc_latestobs.sql
--      updated for PostgreSQL August 2004
-- 
--     2/15/2005:   Fixed problem with duplicate key violates unique constraint

CREATE OR REPLACE FUNCTION latestobs
	(
	 varchar(8),
	 varchar(2),
	 integer,
	 varchar(2),
	 varchar(1),
	 timestamp,
	 float8,
	 varchar(1),
	 integer,
	 integer,
	 varchar(10),
	 timestamp,
	 timestamp,
	 integer
	)
	
RETURNS INT     AS '
	--
	--	Local variables
	--
	DECLARE lobs         latestobsvalue%ROWTYPE;
                newlid       ALIAS FOR  $1;
                newpe        ALIAS FOR  $2;
                newdur       ALIAS FOR  $3;
                newts        ALIAS FOR  $4;
                newextr      ALIAS FOR  $5;
                newobstime   ALIAS FOR  $6;
                newvalue     ALIAS FOR  $7;
                newrawqual   ALIAS FOR  $8;
                newqc        ALIAS FOR  $9;
                newrev       ALIAS FOR  $10;
                newprid      ALIAS FOR  $11;
                newprodtime  ALIAS FOR  $12;
                newposttime  ALIAS FOR  $13;
                do_update    ALIAS FOR  $14;

	--
	--	Process latestobs data
	--
BEGIN
         if do_update = 1 or do_update = 2 then
             UPDATE latestobsvalue SET
                 obstime        = newobstime,
                 value          = newvalue,
                 shef_qual_code = newrawqual,
                 quality_code   = newqc, 
                 revision       = newrev,
                 product_id     = newprid,
                 producttime    = newprodtime,
                 postingtime    = newposttime
                 WHERE lid = newlid and pe = newpe and
                 dur = newdur and ts = newts and
                 extremum = newextr and obstime <= newobstime;  
         else
             UPDATE latestobsvalue SET
                 obstime        = newobstime,
                 value          = newvalue,
                 shef_qual_code = newrawqual,
                 quality_code   = newqc, 
                 revision       = newrev,
                 product_id     = newprid,
                 producttime    = newprodtime,
                 postingtime    = newposttime
                 WHERE lid = newlid and pe = newpe and
                 dur = newdur and ts = newts and
                 extremum = newextr and obstime < newobstime; 
         end if;
	     
	 if (NOT FOUND) then    
             SELECT INTO lobs * FROM latestobsvalue
             WHERE lid = newlid
             AND   pe = newpe
             AND   dur = newdur
             AND   ts = newts
             AND   extremum = newextr;
             if (NOT FOUND) then    
                 INSERT INTO latestobsvalue
                 VALUES ( newlid,      newpe,       newdur,   newts,
                          newextr,     newobstime,  newvalue, newrev,
		          newrawqual,  newqc,       newprid,
		          newprodtime, newposttime);
	     end if;
	 end if;	

RETURN 0;
END;
'  LANGUAGE 'plpgsql';

-------------------------------------------------------------------------------