Artifact
c376e51c3fde27975123283ea564249e7c6cdc9ae1c6b3ae1ca416d98aca43d1:
0000: 43 52 45 41 54 45 20 50 52 4f 43 45 44 55 52 41 CREATE PROCEDURA
0010: 4c 20 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 73 L LANGUAGE plpgs
0020: 71 6c 3b 0a 0a 2d 2d 20 67 65 6e 65 72 61 6c 20 ql;..-- general
0030: 61 72 72 61 79 20 73 6f 72 74 69 6e 67 20 66 75 array sorting fu
0040: 6e 63 74 69 6f 6e 73 0a 2d 2d 20 73 6f 72 74 73 nctions.-- sorts
0050: 20 61 72 72 61 79 0a 43 52 45 41 54 45 20 6f 72 array.CREATE or
0060: 20 72 65 70 6c 61 63 65 20 46 55 4e 43 54 49 4f replace FUNCTIO
0070: 4e 20 73 6f 72 74 28 6f 72 69 67 69 6e 61 6c 20 N sort(original
0080: 61 6e 79 61 72 72 61 79 29 20 52 45 54 55 52 4e anyarray) RETURN
0090: 53 20 61 6e 79 61 72 72 61 79 0a 09 4c 41 4e 47 S anyarray..LANG
00a0: 55 41 47 45 20 73 71 6c 20 49 4d 4d 55 54 41 42 UAGE sql IMMUTAB
00b0: 4c 45 20 53 54 52 49 43 54 0a 09 41 53 20 24 5f LE STRICT..AS $_
00c0: 24 0a 73 65 6c 65 63 74 20 61 72 72 61 79 5f 61 $.select array_a
00d0: 67 67 28 69 74 65 6d 29 20 61 73 20 72 65 73 75 gg(item) as resu
00e0: 6c 74 20 66 72 6f 6d 20 28 73 65 6c 65 63 74 20 lt from (select
00f0: 75 6e 6e 65 73 74 28 24 31 29 20 61 73 20 69 74 unnest($1) as it
0100: 65 6d 20 6f 72 64 65 72 20 62 79 20 69 74 65 6d em order by item
0110: 29 20 61 3b 0a 24 5f 24 3b 0a 0a 2d 2d 20 73 6f ) a;.$_$;..-- so
0120: 72 74 73 20 61 72 72 61 79 20 61 6e 64 20 72 65 rts array and re
0130: 6d 6f 76 65 73 20 64 75 70 6c 69 63 61 74 65 73 moves duplicates
0140: 0a 43 52 45 41 54 45 20 6f 72 20 72 65 70 6c 61 .CREATE or repla
0150: 63 65 20 46 55 4e 43 54 49 4f 4e 20 75 73 6f 72 ce FUNCTION usor
0160: 74 28 6f 72 69 67 69 6e 61 6c 20 61 6e 79 61 72 t(original anyar
0170: 72 61 79 29 20 52 45 54 55 52 4e 53 20 61 6e 79 ray) RETURNS any
0180: 61 72 72 61 79 0a 09 4c 41 4e 47 55 41 47 45 20 array..LANGUAGE
0190: 73 71 6c 20 49 4d 4d 55 54 41 42 4c 45 20 53 54 sql IMMUTABLE ST
01a0: 52 49 43 54 0a 09 41 53 20 24 5f 24 0a 73 65 6c RICT..AS $_$.sel
01b0: 65 63 74 20 61 72 72 61 79 5f 61 67 67 28 69 74 ect array_agg(it
01c0: 65 6d 29 20 61 73 20 72 65 73 75 6c 74 20 66 72 em) as result fr
01d0: 6f 6d 20 28 73 65 6c 65 63 74 20 64 69 73 74 69 om (select disti
01e0: 6e 63 74 20 75 6e 6e 65 73 74 28 24 31 29 20 61 nct unnest($1) a
01f0: 73 20 69 74 65 6d 20 6f 72 64 65 72 20 62 79 20 s item order by
0200: 69 74 65 6d 29 20 61 3b 0a 24 5f 24 3b 0a 0a 2d item) a;.$_$;..-
0210: 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f 6e 73 - this functions
0220: 20 72 65 74 75 72 6e 73 20 69 64 20 6f 66 20 73 returns id of s
0230: 69 74 65 0a 63 72 65 61 74 65 20 6f 72 20 72 65 ite.create or re
0240: 70 6c 61 63 65 20 66 75 6e 63 74 69 6f 6e 20 67 place function g
0250: 65 74 5f 73 69 74 65 28 6d 79 5f 73 69 74 65 20 et_site(my_site
0260: 74 65 78 74 5b 5d 29 20 72 65 74 75 72 6e 73 20 text[]) returns
0270: 69 6e 74 65 67 65 72 0a 09 6c 61 6e 67 75 61 67 integer..languag
0280: 65 20 70 6c 70 67 73 71 6c 20 73 74 72 69 63 74 e plpgsql strict
0290: 0a 09 61 73 20 24 24 0a 64 65 63 6c 61 72 65 0a ..as $$.declare.
02a0: 09 73 69 74 65 5f 69 64 20 69 6e 74 65 67 65 72 .site_id integer
02b0: 3b 0a 62 65 67 69 6e 0a 09 73 65 6c 65 63 74 20 ;.begin..select
02c0: 69 64 5f 73 69 74 65 20 66 72 6f 6d 20 73 69 74 id_site from sit
02d0: 65 20 77 68 65 72 65 20 6d 79 5f 73 69 74 65 20 e where my_site
02e0: 3d 20 73 69 74 65 20 69 6e 74 6f 20 73 69 74 65 = site into site
02f0: 5f 69 64 3b 0a 09 69 66 20 6e 6f 74 20 66 6f 75 _id;..if not fou
0300: 6e 64 20 74 68 65 6e 0a 09 09 69 6e 73 65 72 74 nd then...insert
0310: 20 69 6e 74 6f 20 73 69 74 65 20 28 73 69 74 65 into site (site
0320: 29 20 76 61 6c 75 65 73 20 28 6d 79 5f 73 69 74 ) values (my_sit
0330: 65 29 3b 0a 09 09 73 65 6c 65 63 74 20 69 64 5f e);...select id_
0340: 73 69 74 65 20 66 72 6f 6d 20 73 69 74 65 20 77 site from site w
0350: 68 65 72 65 20 6d 79 5f 73 69 74 65 20 3d 20 73 here my_site = s
0360: 69 74 65 20 69 6e 74 6f 20 73 69 74 65 5f 69 64 ite into site_id
0370: 3b 0a 09 65 6e 64 20 69 66 3b 0a 09 72 65 74 75 ;..end if;..retu
0380: 72 6e 20 73 69 74 65 5f 69 64 3b 0a 65 6e 64 3b rn site_id;.end;
0390: 0a 24 24 3b 0a 0a 63 72 65 61 74 65 20 6f 72 20 .$$;..create or
03a0: 72 65 70 6c 61 63 65 20 66 75 6e 63 74 69 6f 6e replace function
03b0: 20 67 65 74 5f 73 69 74 65 28 64 6f 6d 61 69 6e get_site(domain
03c0: 20 74 65 78 74 29 20 72 65 74 75 72 6e 73 20 69 text) returns i
03d0: 6e 74 65 67 65 72 0a 09 6c 61 6e 67 75 61 67 65 nteger..language
03e0: 20 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 73 sql immutable s
03f0: 74 72 69 63 74 0a 09 61 73 20 24 24 0a 73 65 6c trict..as $$.sel
0400: 65 63 74 20 67 65 74 5f 73 69 74 65 28 74 72 69 ect get_site(tri
0410: 70 64 6f 6d 61 69 6e 28 24 31 29 29 20 61 73 20 pdomain($1)) as
0420: 72 65 73 75 6c 74 3b 0a 24 24 3b 0a 0a 2d 2d 20 result;.$$;..--
0430: 74 68 69 73 20 66 75 6e 63 74 69 6f 6e 20 61 64 this function ad
0440: 64 73 20 74 61 67 20 74 6f 20 64 6f 6d 61 69 6e ds tag to domain
0450: 0a 43 52 45 41 54 45 20 6f 72 20 72 65 70 6c 61 .CREATE or repla
0460: 63 65 20 46 55 4e 43 54 49 4f 4e 20 6d 61 72 6b ce FUNCTION mark
0470: 28 64 6f 6d 61 69 6e 20 74 65 78 74 2c 20 6e 65 (domain text, ne
0480: 77 5f 74 61 67 20 74 65 78 74 29 20 52 45 54 55 w_tag text) RETU
0490: 52 4e 53 20 69 6e 74 65 67 65 72 0a 09 4c 41 4e RNS integer..LAN
04a0: 47 55 41 47 45 20 73 71 6c 20 69 6d 6d 75 74 61 GUAGE sql immuta
04b0: 62 6c 65 20 53 54 52 49 43 54 0a 09 41 53 20 24 ble STRICT..AS $
04c0: 24 0a 73 65 6c 65 63 74 20 6d 61 72 6b 28 67 65 $.select mark(ge
04d0: 74 5f 73 69 74 65 28 24 31 29 2c 20 24 32 29 20 t_site($1), $2)
04e0: 61 73 20 72 65 73 75 6c 74 3b 0a 24 24 3b 0a 0a as result;.$$;..
04f0: 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 69 6f 6e -- this function
0500: 20 61 64 64 73 20 74 61 67 20 74 6f 20 73 69 74 adds tag to sit
0510: 65 20 62 79 20 73 69 74 65 20 69 64 0a 43 52 45 e by site id.CRE
0520: 41 54 45 20 6f 72 20 72 65 70 6c 61 63 65 20 46 ATE or replace F
0530: 55 4e 43 54 49 4f 4e 20 6d 61 72 6b 28 6d 79 5f UNCTION mark(my_
0540: 69 64 5f 73 69 74 65 20 69 6e 74 65 67 65 72 2c id_site integer,
0550: 20 6e 65 77 5f 74 61 67 20 74 65 78 74 29 20 52 new_tag text) R
0560: 45 54 55 52 4e 53 20 69 6e 74 65 67 65 72 0a 09 ETURNS integer..
0570: 4c 41 4e 47 55 41 47 45 20 70 6c 70 67 73 71 6c LANGUAGE plpgsql
0580: 20 53 54 52 49 43 54 0a 09 41 53 20 24 24 0a 64 STRICT..AS $$.d
0590: 65 63 6c 61 72 65 0a 09 2d 2d 20 6d 61 79 62 65 eclare..-- maybe
05a0: 20 63 68 65 63 6b 20 73 68 6f 75 6c 64 20 62 65 check should be
05b0: 20 61 64 64 65 64 20 74 6f 20 6d 61 6b 65 20 73 added to make s
05c0: 75 72 65 20 73 75 70 70 6c 69 65 64 20 73 69 74 ure supplied sit
05d0: 65 20 69 64 20 72 65 61 6c 6c 79 20 65 78 69 73 e id really exis
05e0: 74 73 0a 09 6d 79 5f 74 61 67 20 74 65 78 74 5b ts..my_tag text[
05f0: 5d 3b 0a 09 6d 79 5f 74 61 67 5f 69 64 20 69 6e ];..my_tag_id in
0600: 74 65 67 65 72 3b 0a 62 65 67 69 6e 0a 09 2d 2d teger;.begin..--
0610: 20 73 65 6c 65 63 74 69 6e 67 20 74 61 67 73 20 selecting tags
0620: 73 69 74 65 20 61 6c 72 65 61 64 79 20 68 61 76 site already hav
0630: 65 20 61 6e 64 20 61 64 64 69 6e 67 20 6e 65 77 e and adding new
0640: 20 74 61 67 20 74 6f 20 74 68 65 6d 0a 09 2d 2d tag to them..--
0650: 20 6e 6f 74 65 20 74 68 61 74 20 74 61 67 73 20 note that tags
0660: 73 68 6f 75 6c 64 20 62 65 20 73 6f 72 74 65 64 should be sorted
0670: 20 74 6f 20 65 6c 69 6d 69 6e 61 74 65 20 70 65 to eliminate pe
0680: 72 6d 75 74 61 74 69 6f 6e 73 0a 09 73 65 6c 65 rmutations..sele
0690: 63 74 20 63 6f 61 6c 65 73 63 65 28 74 61 67 2c ct coalesce(tag,
06a0: 20 27 7b 7d 27 3a 3a 74 65 78 74 5b 5d 29 20 66 '{}'::text[]) f
06b0: 72 6f 6d 20 75 72 6c 73 20 6e 61 74 75 72 61 6c rom urls natural
06c0: 20 6c 65 66 74 20 6a 6f 69 6e 20 74 61 67 20 77 left join tag w
06d0: 68 65 72 65 20 69 64 5f 73 69 74 65 20 3d 20 6d here id_site = m
06e0: 79 5f 69 64 5f 73 69 74 65 20 69 6e 74 6f 20 6d y_id_site into m
06f0: 79 5f 74 61 67 3b 0a 09 69 66 20 6e 6f 74 20 66 y_tag;..if not f
0700: 6f 75 6e 64 20 74 68 65 6e 0a 09 09 2d 2d 20 6e ound then...-- n
0710: 6f 20 72 65 63 6f 72 64 73 20 66 6f 75 6e 64 20 o records found
0720: 2d 20 63 72 65 61 74 69 6e 67 20 6e 65 77 20 74 - creating new t
0730: 61 67 0a 09 09 69 6e 73 65 72 74 20 69 6e 74 6f ag...insert into
0740: 20 75 72 6c 73 20 28 69 64 5f 73 69 74 65 2c 20 urls (id_site,
0750: 69 64 5f 74 61 67 29 20 76 61 6c 75 65 73 20 28 id_tag) values (
0760: 6d 79 5f 69 64 5f 73 69 74 65 2c 20 67 65 74 5f my_id_site, get_
0770: 74 61 67 28 61 72 72 61 79 5b 6e 65 77 5f 74 61 tag(array[new_ta
0780: 67 5d 29 29 3b 0a 09 65 6c 73 65 0a 09 09 2d 2d g]));..else...--
0790: 20 6a 6f 69 6e 69 6e 67 20 74 61 67 73 0a 09 09 joining tags...
07a0: 73 65 6c 65 63 74 20 75 73 6f 72 74 28 6d 79 5f select usort(my_
07b0: 74 61 67 20 7c 7c 20 61 72 72 61 79 5b 6e 65 77 tag || array[new
07c0: 5f 74 61 67 5d 29 20 69 6e 74 6f 20 6d 79 5f 74 _tag]) into my_t
07d0: 61 67 3b 0a 09 09 2d 2d 20 75 70 64 61 74 69 6e ag;...-- updatin
07e0: 67 20 65 78 69 73 74 69 6e 67 20 72 65 63 6f 72 g existing recor
07f0: 64 0a 09 09 75 70 64 61 74 65 20 75 72 6c 73 20 d...update urls
0800: 73 65 74 20 69 64 5f 74 61 67 20 3d 20 67 65 74 set id_tag = get
0810: 5f 74 61 67 28 6d 79 5f 74 61 67 20 7c 7c 20 61 _tag(my_tag || a
0820: 72 72 61 79 5b 6e 65 77 5f 74 61 67 5d 29 20 77 rray[new_tag]) w
0830: 68 65 72 65 20 69 64 5f 73 69 74 65 20 3d 20 6d here id_site = m
0840: 79 5f 69 64 5f 73 69 74 65 3b 0a 09 65 6e 64 20 y_id_site;..end
0850: 69 66 3b 0a 09 72 65 74 75 72 6e 20 6d 79 5f 69 if;..return my_i
0860: 64 5f 73 69 74 65 3b 0a 65 6e 64 3b 0a 24 24 3b d_site;.end;.$$;
0870: 0a 0a 2d 2d 20 74 68 69 73 20 66 75 6e 63 74 69 ..-- this functi
0880: 6f 6e 20 72 65 74 75 72 6e 73 20 69 64 20 6f 66 on returns id of
0890: 20 74 61 67 20 61 72 72 61 79 0a 63 72 65 61 74 tag array.creat
08a0: 65 20 6f 72 20 72 65 70 6c 61 63 65 20 66 75 6e e or replace fun
08b0: 63 74 69 6f 6e 20 67 65 74 5f 74 61 67 28 6d 79 ction get_tag(my
08c0: 5f 74 61 67 20 74 65 78 74 5b 5d 29 20 72 65 74 _tag text[]) ret
08d0: 75 72 6e 73 20 69 6e 74 65 67 65 72 0a 09 6c 61 urns integer..la
08e0: 6e 67 75 61 67 65 20 70 6c 70 67 73 71 6c 20 73 nguage plpgsql s
08f0: 74 72 69 63 74 0a 09 61 73 20 24 24 0a 64 65 63 trict..as $$.dec
0900: 6c 61 72 65 0a 09 74 61 67 5f 69 64 20 69 6e 74 lare..tag_id int
0910: 65 67 65 72 3b 0a 62 65 67 69 6e 0a 09 73 65 6c eger;.begin..sel
0920: 65 63 74 20 69 64 5f 74 61 67 20 66 72 6f 6d 20 ect id_tag from
0930: 74 61 67 20 77 68 65 72 65 20 75 73 6f 72 74 28 tag where usort(
0940: 6d 79 5f 74 61 67 29 20 3d 20 74 61 67 20 69 6e my_tag) = tag in
0950: 74 6f 20 74 61 67 5f 69 64 3b 0a 09 69 66 20 6e to tag_id;..if n
0960: 6f 74 20 66 6f 75 6e 64 20 74 68 65 6e 0a 09 09 ot found then...
0970: 69 6e 73 65 72 74 20 69 6e 74 6f 20 74 61 67 20 insert into tag
0980: 28 74 61 67 29 20 76 61 6c 75 65 73 20 28 75 73 (tag) values (us
0990: 6f 72 74 28 6d 79 5f 74 61 67 29 29 3b 0a 09 09 ort(my_tag));...
09a0: 73 65 6c 65 63 74 20 69 64 5f 74 61 67 20 66 72 select id_tag fr
09b0: 6f 6d 20 74 61 67 20 77 68 65 72 65 20 75 73 6f om tag where uso
09c0: 72 74 28 6d 79 5f 74 61 67 29 20 3d 20 74 61 67 rt(my_tag) = tag
09d0: 20 69 6e 74 6f 20 74 61 67 5f 69 64 3b 0a 09 65 into tag_id;..e
09e0: 6e 64 20 69 66 3b 0a 09 72 65 74 75 72 6e 20 74 nd if;..return t
09f0: 61 67 5f 69 64 3b 0a 65 6e 64 3b 0a 24 24 3b 0a ag_id;.end;.$$;.
0a00: 0a 2d 2d 20 74 72 61 6e 73 66 6f 72 6d 73 20 64 .-- transforms d
0a10: 6f 6d 61 69 6e 20 69 6e 74 6f 20 6f 72 64 65 72 omain into order
0a20: 65 64 20 61 72 72 61 79 20 66 6f 72 20 69 6e 64 ed array for ind
0a30: 65 78 69 6e 67 0a 43 52 45 41 54 45 20 6f 72 20 exing.CREATE or
0a40: 72 65 70 6c 61 63 65 20 46 55 4e 43 54 49 4f 4e replace FUNCTION
0a50: 20 74 72 69 70 64 6f 6d 61 69 6e 28 75 72 6c 20 tripdomain(url
0a60: 74 65 78 74 29 20 52 45 54 55 52 4e 53 20 74 65 text) RETURNS te
0a70: 78 74 5b 5d 0a 09 4c 41 4e 47 55 41 47 45 20 70 xt[]..LANGUAGE p
0a80: 6c 70 67 73 71 6c 20 49 4d 4d 55 54 41 42 4c 45 lpgsql IMMUTABLE
0a90: 20 53 54 52 49 43 54 0a 09 41 53 20 24 5f 24 0a STRICT..AS $_$.
0aa0: 64 65 63 6c 61 72 65 0a 09 72 65 73 75 6c 74 20 declare..result
0ab0: 74 65 78 74 5b 5d 3b 0a 09 73 70 6c 69 74 74 65 text[];..splitte
0ac0: 64 20 74 65 78 74 5b 5d 3b 0a 09 78 20 69 6e 74 d text[];..x int
0ad0: 65 67 65 72 3b 0a 09 6c 65 6e 67 74 68 20 69 6e eger;..length in
0ae0: 74 65 67 65 72 3b 0a 62 65 67 69 6e 0a 09 73 70 teger;.begin..sp
0af0: 6c 69 74 74 65 64 20 3a 3d 20 73 74 72 69 6e 67 litted := string
0b00: 5f 74 6f 5f 61 72 72 61 79 28 24 31 2c 20 27 2e _to_array($1, '.
0b10: 27 29 3b 0a 09 6c 65 6e 67 74 68 20 3a 3d 20 61 ');..length := a
0b20: 72 72 61 79 5f 6c 65 6e 67 74 68 28 73 70 6c 69 rray_length(spli
0b30: 74 74 65 64 2c 20 31 29 3b 0a 09 78 20 3a 3d 20 tted, 1);..x :=
0b40: 31 3b 0a 09 6c 6f 6f 70 0a 09 09 65 78 69 74 20 1;..loop...exit
0b50: 77 68 65 6e 20 73 70 6c 69 74 74 65 64 5b 78 5d when splitted[x]
0b60: 20 69 73 20 6e 75 6c 6c 3b 0a 09 09 72 65 73 75 is null;...resu
0b70: 6c 74 5b 78 5d 20 3a 3d 20 73 70 6c 69 74 74 65 lt[x] := splitte
0b80: 64 5b 78 5d 20 7c 7c 20 27 3a 27 20 7c 7c 20 6c d[x] || ':' || l
0b90: 65 6e 67 74 68 20 2d 20 78 3b 0a 09 09 78 20 3a ength - x;...x :
0ba0: 3d 20 78 20 2b 20 31 3b 0a 09 65 6e 64 20 6c 6f = x + 1;..end lo
0bb0: 6f 70 3b 0a 09 72 65 74 75 72 6e 20 72 65 73 75 op;..return resu
0bc0: 6c 74 3b 0a 65 6e 64 3b 24 5f 24 3b 0a 0a 2d 2d lt;.end;$_$;..--
0bd0: 20 74 72 61 6e 73 66 6f 72 6d 73 20 6f 72 64 65 transforms orde
0be0: 72 65 64 20 61 72 72 61 79 20 69 6e 74 6f 20 64 red array into d
0bf0: 6f 6d 61 69 6e 0a 63 72 65 61 74 65 20 6f 72 20 omain.create or
0c00: 72 65 70 6c 61 63 65 20 66 75 6e 63 74 69 6f 6e replace function
0c10: 20 75 6e 74 72 69 70 28 73 69 74 65 20 74 65 78 untrip(site tex
0c20: 74 5b 5d 29 20 72 65 74 75 72 6e 73 20 74 65 78 t[]) returns tex
0c30: 74 0a 09 6c 61 6e 67 75 61 67 65 20 70 6c 70 67 t..language plpg
0c40: 73 71 6c 20 69 6d 6d 75 74 61 62 6c 65 20 73 74 sql immutable st
0c50: 72 69 63 74 0a 09 61 73 20 24 5f 24 0a 64 65 63 rict..as $_$.dec
0c60: 6c 61 72 65 0a 09 78 20 69 6e 74 65 67 65 72 3b lare..x integer;
0c70: 0a 09 73 70 6c 69 74 74 65 64 20 74 65 78 74 5b ..splitted text[
0c80: 5d 3b 0a 09 70 61 69 72 20 74 65 78 74 5b 5d 3b ];..pair text[];
0c90: 0a 62 65 67 69 6e 0a 09 78 20 3a 3d 20 61 72 72 .begin..x := arr
0ca0: 61 79 5f 6c 65 6e 67 74 68 28 73 69 74 65 2c 20 ay_length(site,
0cb0: 31 29 3b 0a 09 6c 6f 6f 70 20 0a 09 09 65 78 69 1);..loop ...exi
0cc0: 74 20 77 68 65 6e 20 73 69 74 65 5b 78 5d 20 69 t when site[x] i
0cd0: 73 20 6e 75 6c 6c 3b 0a 09 09 70 61 69 72 20 3a s null;...pair :
0ce0: 3d 20 73 74 72 69 6e 67 5f 74 6f 5f 61 72 72 61 = string_to_arra
0cf0: 79 28 73 69 74 65 5b 78 5d 2c 20 27 3a 27 29 3b y(site[x], ':');
0d00: 0a 09 09 73 70 6c 69 74 74 65 64 5b 30 20 2d 20 ...splitted[0 -
0d10: 70 61 69 72 5b 32 5d 3a 3a 69 6e 74 65 67 65 72 pair[2]::integer
0d20: 5d 20 3a 3d 20 70 61 69 72 5b 31 5d 3b 0a 09 09 ] := pair[1];...
0d30: 78 20 3a 3d 20 78 20 2d 20 31 3b 0a 09 65 6e 64 x := x - 1;..end
0d40: 20 6c 6f 6f 70 3b 0a 09 72 65 74 75 72 6e 20 61 loop;..return a
0d50: 72 72 61 79 5f 74 6f 5f 73 74 72 69 6e 67 28 73 rray_to_string(s
0d60: 70 6c 69 74 74 65 64 2c 20 27 2e 27 29 3b 0a 65 plitted, '.');.e
0d70: 6e 64 3b 0a 24 5f 24 3b 0a 0a 2d 2d 20 74 61 62 nd;.$_$;..-- tab
0d80: 6c 65 20 74 6f 20 68 6f 6c 64 20 61 6c 6c 20 72 le to hold all r
0d90: 75 6c 65 73 0a 43 52 45 41 54 45 20 54 41 42 4c ules.CREATE TABL
0da0: 45 20 72 75 6c 65 73 20 28 0a 09 6e 65 74 6d 61 E rules (..netma
0db0: 73 6b 20 63 69 64 72 20 4e 4f 54 20 4e 55 4c 4c sk cidr NOT NULL
0dc0: 2c 0a 09 72 65 64 69 72 65 63 74 5f 75 72 6c 20 ,..redirect_url
0dd0: 74 65 78 74 20 44 45 46 41 55 4c 54 20 27 61 62 text DEFAULT 'ab
0de0: 6f 75 74 3a 3a 62 6c 61 6e 6b 27 3a 3a 74 65 78 out::blank'::tex
0df0: 74 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 66 72 6f t NOT NULL,..fro
0e00: 6d 5f 77 65 65 6b 64 61 79 20 73 6d 61 6c 6c 69 m_weekday smalli
0e10: 6e 74 20 44 45 46 41 55 4c 54 20 30 20 4e 4f 54 nt DEFAULT 0 NOT
0e20: 20 4e 55 4c 4c 2c 0a 09 74 6f 5f 77 65 65 6b 64 NULL,..to_weekd
0e30: 61 79 20 73 6d 61 6c 6c 69 6e 74 20 44 45 46 41 ay smallint DEFA
0e40: 55 4c 54 20 36 20 4e 4f 54 20 4e 55 4c 4c 2c 0a ULT 6 NOT NULL,.
0e50: 09 66 72 6f 6d 5f 74 69 6d 65 20 74 69 6d 65 20 .from_time time
0e60: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
0e70: 65 20 44 45 46 41 55 4c 54 20 27 30 30 3a 30 30 e DEFAULT '00:00
0e80: 3a 30 30 27 3a 3a 74 69 6d 65 20 77 69 74 68 6f :00'::time witho
0e90: 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 4e 4f 54 ut time zone NOT
0ea0: 20 4e 55 4c 4c 2c 0a 09 74 6f 5f 74 69 6d 65 20 NULL,..to_time
0eb0: 74 69 6d 65 20 77 69 74 68 6f 75 74 20 74 69 6d time without tim
0ec0: 65 20 7a 6f 6e 65 20 44 45 46 41 55 4c 54 20 27 e zone DEFAULT '
0ed0: 32 33 3a 35 39 3a 35 39 27 3a 3a 74 69 6d 65 20 23:59:59'::time
0ee0: 77 69 74 68 6f 75 74 20 74 69 6d 65 20 7a 6f 6e without time zon
0ef0: 65 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 69 64 5f e NOT NULL,..id_
0f00: 74 61 67 20 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 tag smallint NOT
0f10: 20 4e 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 20 NULL.);..ALTER
0f20: 54 41 42 4c 45 20 4f 4e 4c 59 20 72 75 6c 65 73 TABLE ONLY rules
0f30: 0a 09 41 44 44 20 43 4f 4e 53 54 52 41 49 4e 54 ..ADD CONSTRAINT
0f40: 20 72 75 6c 65 73 5f 70 6b 65 79 20 50 52 49 4d rules_pkey PRIM
0f50: 41 52 59 20 4b 45 59 20 28 6e 65 74 6d 61 73 6b ARY KEY (netmask
0f60: 29 3b 0a 0a 2d 2d 20 74 61 62 6c 65 20 74 6f 20 );..-- table to
0f70: 68 6f 6c 64 20 73 69 74 65 20 61 72 72 61 79 73 hold site arrays
0f80: 0a 43 52 45 41 54 45 20 54 41 42 4c 45 20 73 69 .CREATE TABLE si
0f90: 74 65 20 28 0a 09 69 64 5f 73 69 74 65 20 73 65 te (..id_site se
0fa0: 72 69 61 6c 2c 0a 09 73 69 74 65 20 74 65 78 74 rial,..site text
0fb0: 5b 5d 20 4e 4f 54 20 4e 55 4c 4c 0a 29 3b 0a 0a [] NOT NULL.);..
0fc0: 41 4c 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 ALTER TABLE ONLY
0fd0: 20 73 69 74 65 0a 09 41 44 44 20 43 4f 4e 53 54 site..ADD CONST
0fe0: 52 41 49 4e 54 20 73 69 74 65 5f 70 6b 65 79 20 RAINT site_pkey
0ff0: 50 52 49 4d 41 52 59 20 4b 45 59 20 28 69 64 5f PRIMARY KEY (id_
1000: 73 69 74 65 29 3b 0a 0a 43 52 45 41 54 45 20 55 site);..CREATE U
1010: 4e 49 51 55 45 20 49 4e 44 45 58 20 73 69 74 65 NIQUE INDEX site
1020: 5f 75 20 4f 4e 20 73 69 74 65 20 28 75 73 6f 72 _u ON site (usor
1030: 74 28 73 69 74 65 29 29 3b 0a 0a 43 52 45 41 54 t(site));..CREAT
1040: 45 20 49 4e 44 45 58 20 73 69 74 65 5f 67 20 4f E INDEX site_g O
1050: 4e 20 73 69 74 65 20 55 53 49 4e 47 20 67 69 6e N site USING gin
1060: 20 28 73 69 74 65 29 3b 0a 0a 2d 2d 20 74 61 62 (site);..-- tab
1070: 6c 65 20 74 6f 20 68 6f 6c 64 20 74 61 67 20 63 le to hold tag c
1080: 6f 6d 62 69 6e 61 74 69 6f 6e 73 0a 43 52 45 41 ombinations.CREA
1090: 54 45 20 54 41 42 4c 45 20 74 61 67 20 28 0a 09 TE TABLE tag (..
10a0: 69 64 5f 74 61 67 20 73 65 72 69 61 6c 2c 0a 09 id_tag serial,..
10b0: 74 61 67 20 74 65 78 74 5b 5d 20 4e 4f 54 20 4e tag text[] NOT N
10c0: 55 4c 4c 0a 29 3b 0a 0a 41 4c 54 45 52 20 54 41 ULL.);..ALTER TA
10d0: 42 4c 45 20 4f 4e 4c 59 20 74 61 67 0a 09 41 44 BLE ONLY tag..AD
10e0: 44 20 43 4f 4e 53 54 52 41 49 4e 54 20 74 61 67 D CONSTRAINT tag
10f0: 5f 70 6b 65 79 20 50 52 49 4d 41 52 59 20 4b 45 _pkey PRIMARY KE
1100: 59 20 28 69 64 5f 74 61 67 29 3b 0a 0a 43 52 45 Y (id_tag);..CRE
1110: 41 54 45 20 55 4e 49 51 55 45 20 49 4e 44 45 58 ATE UNIQUE INDEX
1120: 20 74 61 67 5f 75 20 4f 4e 20 74 61 67 20 28 75 tag_u ON tag (u
1130: 73 6f 72 74 28 74 61 67 29 29 3b 0a 0a 43 52 45 sort(tag));..CRE
1140: 41 54 45 20 49 4e 44 45 58 20 74 61 67 5f 67 20 ATE INDEX tag_g
1150: 4f 4e 20 74 61 67 20 55 53 49 4e 47 20 67 69 6e ON tag USING gin
1160: 20 28 74 61 67 29 3b 0a 0a 2d 2d 20 74 61 62 6c (tag);..-- tabl
1170: 65 20 74 6f 20 68 6f 6c 64 20 74 61 67 20 2d 20 e to hold tag -
1180: 73 69 74 65 20 6c 69 6e 6b 73 0a 43 52 45 41 54 site links.CREAT
1190: 45 20 54 41 42 4c 45 20 75 72 6c 73 20 28 0a 09 E TABLE urls (..
11a0: 64 61 74 65 5f 61 64 64 65 64 20 74 69 6d 65 73 date_added times
11b0: 74 61 6d 70 20 77 69 74 68 6f 75 74 20 74 69 6d tamp without tim
11c0: 65 20 7a 6f 6e 65 20 44 45 46 41 55 4c 54 20 28 e zone DEFAULT (
11d0: 27 6e 6f 77 27 3a 3a 74 65 78 74 29 3a 3a 74 69 'now'::text)::ti
11e0: 6d 65 73 74 61 6d 70 28 30 29 20 77 69 74 68 6f mestamp(0) witho
11f0: 75 74 20 74 69 6d 65 20 7a 6f 6e 65 20 4e 4f 54 ut time zone NOT
1200: 20 4e 55 4c 4c 2c 0a 09 69 64 5f 73 69 74 65 20 NULL,..id_site
1210: 73 6d 61 6c 6c 69 6e 74 20 4e 4f 54 20 4e 55 4c smallint NOT NUL
1220: 4c 2c 0a 09 69 64 5f 74 61 67 20 73 6d 61 6c 6c L,..id_tag small
1230: 69 6e 74 20 4e 4f 54 20 4e 55 4c 4c 2c 0a 09 72 int NOT NULL,..r
1240: 65 67 65 78 20 74 65 78 74 0a 29 3b 0a 0a 41 4c egex text.);..AL
1250: 54 45 52 20 54 41 42 4c 45 20 4f 4e 4c 59 20 75 TER TABLE ONLY u
1260: 72 6c 73 0a 09 41 44 44 20 43 4f 4e 53 54 52 41 rls..ADD CONSTRA
1270: 49 4e 54 20 75 72 6c 73 5f 70 6b 65 79 20 50 52 INT urls_pkey PR
1280: 49 4d 41 52 59 20 4b 45 59 20 28 64 61 74 65 5f IMARY KEY (date_
1290: 61 64 64 65 64 29 3b 0a 0a 43 52 45 41 54 45 20 added);..CREATE
12a0: 55 4e 49 51 55 45 20 49 4e 44 45 58 20 75 72 6c UNIQUE INDEX url
12b0: 73 5f 69 64 5f 73 69 74 65 20 4f 4e 20 75 72 6c s_id_site ON url
12c0: 73 20 55 53 49 4e 47 20 62 74 72 65 65 20 28 69 s USING btree (i
12d0: 64 5f 73 69 74 65 29 3b 0a 0a 43 52 45 41 54 45 d_site);..CREATE
12e0: 20 55 4e 49 51 55 45 20 49 4e 44 45 58 20 75 72 UNIQUE INDEX ur
12f0: 6c 73 5f 69 64 5f 74 61 67 20 4f 4e 20 75 72 6c ls_id_tag ON url
1300: 73 20 55 53 49 4e 47 20 62 74 72 65 65 20 28 69 s USING btree (i
1310: 64 5f 74 61 67 29 3b 0a 0a 2d 2d 20 72 75 6c 65 d_tag);..-- rule
1320: 20 74 6f 20 6a 6f 69 6e 20 61 6c 6c 20 74 61 62 to join all tab
1330: 6c 65 73 20 69 6e 74 6f 20 6f 6e 65 20 74 6f 20 les into one to
1340: 73 69 6d 70 6c 69 66 79 20 61 63 63 65 73 73 0a simplify access.
1350: 2d 2d 20 61 75 74 6f 6d 61 74 69 63 61 6c 6c 20 -- automaticall
1360: 75 73 65 73 20 63 75 72 72 65 6e 74 20 64 61 79 uses current day
1370: 20 61 6e 64 20 74 69 6d 65 20 64 61 74 61 0a 43 and time data.C
1380: 52 45 41 54 45 20 56 49 45 57 20 73 69 74 65 5f REATE VIEW site_
1390: 72 75 6c 65 20 41 53 0a 53 45 4c 45 43 54 20 61 rule AS.SELECT a
13a0: 2e 72 65 64 69 72 65 63 74 5f 75 72 6c 2c 20 61 .redirect_url, a
13b0: 2e 6e 65 74 6d 61 73 6b 2c 20 62 2e 73 69 74 65 .netmask, b.site
13c0: 2c 20 62 2e 72 65 67 65 78 70 0a 46 52 4f 4d 20 , b.regexp.FROM
13d0: 28 28 0a 09 53 45 4c 45 43 54 20 72 75 6c 65 73 ((..SELECT rules
13e0: 2e 72 65 64 69 72 65 63 74 5f 75 72 6c 2c 20 74 .redirect_url, t
13f0: 61 67 2e 74 61 67 20 41 53 20 72 75 6c 65 5f 74 ag.tag AS rule_t
1400: 61 67 2c 20 72 75 6c 65 73 2e 6e 65 74 6d 61 73 ag, rules.netmas
1410: 6b 0a 09 46 52 4f 4d 20 72 75 6c 65 73 20 4e 41 k..FROM rules NA
1420: 54 55 52 41 4c 20 4a 4f 49 4e 20 74 61 67 0a 09 TURAL JOIN tag..
1430: 57 48 45 52 45 20 28 27 6e 6f 77 27 3a 3a 74 65 WHERE ('now'::te
1440: 78 74 29 3a 3a 74 69 6d 65 20 77 69 74 68 6f 75 xt)::time withou
1450: 74 20 74 69 6d 65 20 7a 6f 6e 65 20 3e 3d 20 72 t time zone >= r
1460: 75 6c 65 73 2e 66 72 6f 6d 5f 74 69 6d 65 0a 09 ules.from_time..
1470: 09 41 4e 44 20 28 27 6e 6f 77 27 3a 3a 74 65 78 .AND ('now'::tex
1480: 74 29 3a 3a 74 69 6d 65 20 77 69 74 68 6f 75 74 t)::time without
1490: 20 74 69 6d 65 20 7a 6f 6e 65 20 3c 3d 20 72 75 time zone <= ru
14a0: 6c 65 73 2e 74 6f 5f 74 69 6d 65 0a 09 09 41 4e les.to_time...AN
14b0: 44 20 64 61 74 65 5f 70 61 72 74 28 27 64 6f 77 D date_part('dow
14c0: 27 3a 3a 74 65 78 74 2c 20 6e 6f 77 28 29 29 20 '::text, now())
14d0: 3e 3d 20 28 72 75 6c 65 73 2e 66 72 6f 6d 5f 77 >= (rules.from_w
14e0: 65 65 6b 64 61 79 29 3a 3a 64 6f 75 62 6c 65 20 eekday)::double
14f0: 70 72 65 63 69 73 69 6f 6e 0a 09 09 41 4e 44 20 precision...AND
1500: 64 61 74 65 5f 70 61 72 74 28 27 64 6f 77 27 3a date_part('dow':
1510: 3a 74 65 78 74 2c 20 6e 6f 77 28 29 29 20 3c 3d :text, now()) <=
1520: 20 28 72 75 6c 65 73 2e 74 6f 5f 77 65 65 6b 64 (rules.to_weekd
1530: 61 79 29 3a 3a 64 6f 75 62 6c 65 20 70 72 65 63 ay)::double prec
1540: 69 73 69 6f 6e 0a 29 20 61 20 4a 4f 49 4e 20 28 ision.) a JOIN (
1550: 0a 09 53 45 4c 45 43 54 20 73 69 74 65 2e 73 69 ..SELECT site.si
1560: 74 65 2c 20 74 61 67 2e 74 61 67 20 41 53 20 75 te, tag.tag AS u
1570: 72 6c 5f 74 61 67 2c 20 72 65 67 65 78 70 0a 09 rl_tag, regexp..
1580: 46 52 4f 4d 20 75 72 6c 73 20 4e 41 54 55 52 41 FROM urls NATURA
1590: 4c 20 4a 4f 49 4e 20 74 61 67 20 4e 41 54 55 52 L JOIN tag NATUR
15a0: 41 4c 20 4a 4f 49 4e 20 73 69 74 65 0a 29 20 62 AL JOIN site.) b
15b0: 20 4f 4e 20 28 62 2e 75 72 6c 5f 74 61 67 20 26 ON (b.url_tag &
15c0: 26 20 61 2e 72 75 6c 65 5f 74 61 67 29 29 3b 0a & a.rule_tag));.