CHuKgTi7

1CREATE OR REPLACE FUNCTION scm_geoip.rand_region_ipv4(country VARCHAR[], region VARCHAR[][]) 
2   RETURNS INET AS $$
3DECLARE
4low BIGINT;
5high BIGINT;
6rand_ip BIGINT;
7sql VARCHAR;
8temp VARCHAR;
9temp_a VARCHAR[];
10temp_r VARCHAR[];
11BEGIN
12   FOR i IN 1.. array_upper(country, 1)
13   LOOP
14     country[i] = '''' || country[i] || '''';
15   END LOOP;
16   FOR i IN 1 .. array_upper(region,1)
17   LOOP
18        temp = 'country_code IN (' || country[i] || ') ';
19        FOR j IN 1 .. array_upper(region,1)
20        LOOP
21            region[i][j] = '''' || region[i][j] || '''';
22            temp_a = array_append(temp_a,region[i][j]);
23        END LOOP;
24        temp = temp || 'AND region_name IN (' || array_to_string(temp_a,',') || ')';
25        temp_a = NULL;
26        temp_r = array_append(temp_r, temp);
27   END LOOP;
28   temp = array_to_string(temp_r, ' OR ');
29
30   CREATE TEMP TABLE IF NOT EXISTS temp_table AS
31          SELECT co.country_code, r.region_name, i.ip_from, i.ip_to
32          FROM
33          scm_geoip.tbl_ipv4_records i
34          INNER JOIN scm_geoip.tbl_city_master c ON c.city_id_pk = i.city_id_fk
35          INNER JOIN scm_geoip.tbl_region_master r ON r.region_id_pk = c.region_id_fk
36          INNER JOIN scm_geoip.tbl_country_master co ON co.country_id_pk = r.country_id_fk;
37
38   sql :=  'SELECT ip_from, ip_to FROM temp_table WHERE '|| temp ||' ORDER BY RANDOM() LIMIT 1';
39   EXECUTE sql
40   INTO low, high;
41   rand_ip =  floor(random()* (high-low + 1) + low);
42   DROP TABLE temp_table;
43   RETURN '0.0.0.0'::inet + rand_ip;
44END;
45$$ language 'plpgsql' STRICT;