CricketTeamSelection/src/Infrastructure/Database/TeamSelectionFunction.sql at main · srinivasmummareddy/CricketTeamSelection · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- FUNCTION: CricketSelection.selectedteam(real, real, integer, integer, integer)

-- DROP FUNCTION "CricketSelection".selectedteam(real, real, integer, integer, integer);

CREATE OR REPLACE FUNCTION "CricketSelection".selectedteam(
	height real,
	bmi real,
	runs integer,
	wickets integer,
	stumpings integer)
    RETURNS TABLE("playerId" integer, "playerName" character varying, "playerCountry" character varying, "playerType" character varying, "playerHeight" real, "playerBmi" real, "playerRuns" integer, "playerWickets" integer, "playerStumpings" integer)
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
(select * from "CricketSelection"."Players" where "playerType"= 'batsman' and
"playerRuns" >runs and "playerBmi"<bmi and "playerHeight">height Limit 5)
union
(select * from "CricketSelection"."Players" where "playerType"= 'bowler' and "playerWickets" > wickets
and "playerBmi"<bmi and "playerHeight">height Limit 5)
union
(select * from "CricketSelection"."Players" where "playerType"= 'keeper' and
"playerStumpings" > stumpings and "playerBmi"<bmi and "playerHeight">height Limit 1)
$BODY$;

ALTER FUNCTION "CricketSelection".selectedteam(real, real, integer, integer, integer)
    OWNER TO postgres;