Forums PostgreSQL.fr

CREATE OR REPLACE RULE ri_qry_add_plot AS
ON INSERT TO qry_add_plot DO INSTEAD  INSERT INTO
t_plot(id_block, plot, id_mod)
VALUES (
  fct_return_or_create_id_block(new.id_expe, new.block),
  new.plot,
  fct_return_or_create_id_mod(new.genotype, new.fungicide, new.nitrogen,
    new.degrain)
);

DROP FUNCTION IF EXISTS fct_return_datestplus(date, integer, double precision) CASCADE;
CREATE OR REPLACE FUNCTION fct_return_datestplus(p_dat_initiale date, p_station integer, p_st double precision)
  RETURNS date AS
$BODY$
  DECLARE
  v_date date;
  BEGIN
      -- récupération de la date correspondante
	SELECT INTO v_date dat_meteo
	FROM (
	    SELECT dat_meteo, sum(tmoy) OVER (ORDER BY dat_meteo) as cumul
	    FROM qry_var_tmoy
	    WHERE dat_meteo > (p_dat_initiale) AND id_station = p_station
	) s
	WHERE s.cumul >= p_st
	ORDER BY dat_meteo
	LIMIT 1;
    RETURN v_date;
  END 
;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
COMMENT ON FUNCTION fct_return_datestplus(date, integer, double precision) IS '
fct_return_datestplus: fonction permettant de calculer la date a partir d''une 
date initiale et d''un cumul de temperature depuis cette date.
Remarques :
- le cumul commence le lendemain de la date initiale renseignee > (p_dat_initiale).
- pas de prise en compte d''une temperature de base ou temperature max 
EXAMPLE 1 :
SELECT fct_return_datestplus(''2007-05-08'', 1801, 30);
';

CREATE FUNCTION _final_median(anyarray) RETURNS float8 AS $$ 
  WITH q AS
  (
     SELECT val
     FROM unnest($1) val
     WHERE VAL IS NOT NULL
     ORDER BY 1
  ),
  cnt AS
  (
    SELECT COUNT(*) AS c FROM q
  )
  SELECT AVG(val)::float8
  FROM 
  (
    SELECT val FROM q
    LIMIT  2 - MOD((SELECT c FROM cnt), 2)
    OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)  
  ) q2;
$$ LANGUAGE sql IMMUTABLE;
 
CREATE AGGREGATE median(anyelement) (
  SFUNC=array_append,
  STYPE=anyarray,
  FINALFUNC=_final_median,
  INITCOND='{}'
);

SELECT num_year, id_station, txt_variety, txt_stage,
	cast(median(dat_stage-'1900-03-01') as integer) + '1900-03-01'::date dat_stage_median
FROM t_plot JOIN t_stage ON t_plot.id_plot=t_stage.id_plot
GROUP BY num_year, id_station, txt_variety, txt_stage
ORDER BY num_year, id_station, txt_variety, txt_stage