Differences

This shows you the differences between two versions of the page.

--- grandnationalpostgresql [2021/08/12 14:30] – created z0hpvk
+++ grandnationalpostgresql [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 76: / Line 76: @@
 <code plsql>
 -- Initialise the SWEEPSTAKE table
-create or replace procedure init as
+-- PostgreSQL Version 11 and Above
-begin
+create or replace procedure init()
-  EXECUTE IMMEDIATE 'delete from sweepstake';
+LANGUAGE SQL
-  EXECUTE IMMEDIATE 'insert into sweepstake (HorseNo, HorseName) (select horseno, name from horses)';
+as $$
-end;
+ delete from sweepstake;
-/
+ insert into sweepstake (HorseNo, HorseName) (select horseno, name from horses);
+$$;
+-- PostgreSQL Version 10 and Below
+create or replace function init()
+RETURNS void AS $$
+BEGIN
+ delete from sweepstake;
+ insert into sweepstake (HorseNo, HorseName) (select horseno, name from horses);
+END;
+$$ LANGUAGE plpgsql;
 -- Performs one round of the draw.
-create or replace procedure OneCycle as
+create or replace function OneCycle()
-Cursor C1 is select PlayerID, PlayerName, horseno, HorseName from (
+RETURNS void AS $$
-	       select row_number() over (order by dbms_random.value ASC) row_num,
+DECLARE
-	              HorseNo, HorseName from sweepstake where playerid is null),
+  C1 CURSOR FOR select PlayerID, PlayerName, horseno, HorseName from (
-	      (select id PlayerID, name PlayerName from participants)
+	          select row_number() over (order by random() ASC) row_num,
-	     where row_num = PlayerID;
+	                 HorseNo, HorseName from sweepstake where playerid is null) as T1,
+	         (select id PlayerID, name PlayerName from participants) as T2
+	        where row_num = PlayerID;
 BEGIN
-FOR a IN C1
+  FOR a IN C1
-LOOP
+  LOOP
-  EXECUTE IMMEDIATE 'Update sweepstake set PlayerID = :pid where HorseNo = :horseno'
+    Update sweepstake set PlayerID = a.PlayerID where HorseNo = a.HorseNo;
-                     using a.PlayerID, a.HorseNo;
+  END LOOP;
-END LOOP;
 END;
-/
+$$ LANGUAGE plpgsql;
 -- Generate the sweepstake data
-create or replace procedure GenSweepstake as
+create or replace function GenSweepstake()
-Cursor C1 is select name, horseno, horsename
+RETURNS void AS $$
-             from participants, sweepstake
+DECLARE
-             where id = playerid order by horseno;
+  C1 CURSOR FOR select name, horseno, horsename
-NoParticipants number(2);
+                from participants, sweepstake
-NoHorses       number(3);
+                where id = playerid order by horseno;
-NoCycles       number(2);
+  NoParticipants real;
-begin
+  NoHorses       real;
-select count(*) into NoParticipants from participants;
+  NoCycles       smallint;
-select count(*) into NoHorses from horses;
+BEGIN
-NoCycles := ceil(NoHorses/NoParticipants);
+  select count(*) into NoParticipants from participants;
-  init;
+  select count(*) into NoHorses from horses;
+  NoCycles := ceil(NoHorses/NoParticipants);
+    perform init();
 FOR a IN 1..NoCycles
 LOOP
-  OneCycle;
+  perform OneCycle();
 END LOOP;
+END;
-dbms_output.put_line(' ');
+$$ LANGUAGE plpgsql;
-dbms_output.put_line('PLAYER    NUMBER  HORSE NAME');
-dbms_output.put_line('------    ------  ------------------');
-FOR b in C1
-LOOP
-   dbms_output.put_line(rpad(b.name, 10) || rpad(b.horseno, 8) || rpad(b.horsename, 30));
-END LOOP;
-end;
-/
 </code>
@@ Line 146: / Line 151: @@
 <code plsql>
-set serveroutput on
+SELECT GenSweepstake;
-BEGIN
-  GenSweepstake();
-END;
-/
 </code>