| Version 1 (modified by celine, 15 years ago) (diff) |
|---|
back to first page ..
back to CookBook Eda
back to CCM2 download and load
-- it is necessary to create a type to hold the output type
DROP TYPE IF EXISTS wso1_id;
CREATE TYPE wso1_id as (wso1_id int);
-- In the function I tried to create a wso type to simplify the text. Pb you need to specify
-- a tablename.columname%TYPE which will have to change along with the column, hence the big ugly script below...
DROP FUNCTION IF EXISTS ccm21.mycatchment(gid_ numeric);
CREATE OR REPLACE FUNCTION ccm21.mycatchment(gid_ numeric) RETURNS setof int AS $$
DECLARE
result RECORD;
pri wso1_id%rowtype;
resultcount RECORD;
BEGIN
SELECT INTO result strahler FROM ccm21.riversegments where gid=gid_;
RAISE NOTICE 'result is %' ,result;
CASE
WHEN result.strahler=2 THEN
RAISE NOTICE 'WSO_2 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso2_id =
(select wso2_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso2_id =
(select wso2_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=3 THEN
RAISE NOTICE 'WSO_3 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso3_id =
(select wso3_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso3_id =
(select wso3_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=4 THEN
RAISE NOTICE 'WSO_4 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso4_id =
(select wso4_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso4_id =
(select wso4_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=5 THEN
RAISE NOTICE 'WSO_5 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso5_id =
(select wso5_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso5_id =
(select wso5_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=6 THEN
RAISE NOTICE 'WSO_6 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso6_id =
(select wso6_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso6_id =
(select wso6_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=7 THEN
RAISE NOTICE 'WSO_7 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso7_id =
(select wso7_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso7_id =
(select wso7_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=8 THEN
RAISE NOTICE 'WSO_8 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso8_id =
(select wso8_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso8_id =
(select wso8_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=9 THEN
RAISE NOTICE 'WSO_9 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso9_id =
(select wso9_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso9_id =
(select wso9_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
WHEN result.strahler=10 THEN
RAISE NOTICE 'WSO_10 used';
SELECT INTO resultcount count(*) from ccm21.catchments c where wso10_id =
(select wso10_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_);
RAISE NOTICE 'number of primary catchments in the catchment =%',resultcount;
for pri in select wso1_id from ccm21.catchments c where wso10_id =
(select wso10_id from ccm21.riversegments r
join ccm21.catchments c on r.wso1_id=c.wso1_id
where r.gid=gid_) loop
pri.wso1_id=CAST(pri.wso1_id AS int8);
return next pri.wso1_id;
end loop;
return;
ELSE RAISE NOTICE 'Strahler is larger than this function can handle !';
END CASE;
RETURN;
END;
$$
LANGUAGE 'plpgsql' ;
COMMENT ON FUNCTION ccm21.mycatchment (gid_ numeric) IS 'Uses the gid from ccm21.riversegments, checks strahler, and select the catchment accordingly, then returns all primary catchments from this larger catchment';
