Forge
New repositoryImport repositoryNew issue
Your repositories Your dashboard Sign in

cloudflare/SortaSQL

Public

mirrored fromhttps://github.com/cloudflare/SortaSQL

Watch0Fork0Star0
CodeCommitsIssuesPull requestsActionsInsightsSecurity
master

Branches

Tags

  • No tags available.
0Branches0Tags
Go to file
Add file
Code

Clone

HTTPS
SSH

Download ZIP

pg_kc.sql

134lines · modecode

RawDownload
adding an example to help people get started75bdea2
15 years ago
1CREATE LANGUAGE plpgsql;
2CREATE TYPE kc_row;
3
4DROP FUNCTION kc_row_in(cstring) CASCADE;
5CREATE FUNCTION kc_row_in(cstring)
6 RETURNS kc_row
7 AS 'libpg_kc', 'kc_row_in'
8 LANGUAGE C IMMUTABLE STRICT;
9
10DROP FUNCTION kc_row_out(kc_row) CASCADE;
11CREATE FUNCTION kc_row_out(kc_row)
12 RETURNS cstring
13 AS 'libpg_kc' , 'kc_row_out'
14 LANGUAGE C IMMUTABLE STRICT;
15
16CREATE TYPE kc_row (
17 INTERNALLENGTH = variable,
18 INPUT = kc_row_in,
19 OUTPUT = kc_row_out,
20 ALIGNMENT = double,
21 STORAGE = plain
22);
23
24DROP FUNCTION kc_key(kc_row);
25CREATE FUNCTION kc_key(kc_row)
26 RETURNS text
27 AS 'libpg_kc', 'kc_key'
28 LANGUAGE C IMMUTABLE STRICT;
29
30CREATE FUNCTION kc_val(kc_row)
31 RETURNS bigint
32 AS 'libpg_kc', 'kc_val'
33 LANGUAGE C IMMUTABLE STRICT;
34
35DROP FUNCTION kc_class(kc_row);
36CREATE FUNCTION kc_class(kc_row)
37 RETURNS text
38 AS 'libpg_kc', 'kc_class'
39 LANGUAGE C IMMUTABLE STRICT;
40
41DROP FUNCTION kc_doctype(kc_row);
42CREATE FUNCTION kc_doctype(kc_row)
43 RETURNS text
44 AS 'libpg_kc', 'kc_doctype'
45 LANGUAGE C IMMUTABLE STRICT;
46
47DROP FUNCTION kc_pop(kc_row);
48CREATE FUNCTION kc_pop(kc_row)
49 RETURNS text
50 AS 'libpg_kc', 'kc_pop'
51 LANGUAGE C IMMUTABLE STRICT;
52
53DROP FUNCTION kc_psource(kc_row);
54CREATE FUNCTION kc_psource(kc_row)
55 RETURNS text
56 AS 'libpg_kc', 'kc_psource'
57 LANGUAGE C IMMUTABLE STRICT;
58
59CREATE OR REPLACE FUNCTION kc_sum(bigint, kc_row)
60 RETURNS bigint
61 AS 'libpg_kc', 'kc_sum'
62 LANGUAGE C IMMUTABLE;
63
64CREATE AGGREGATE sum ( kc_row ) (
65 SFUNC = kc_sum,
66 INITCOND = 0,
67 STYPE = bigint
68);
69
70CREATE OR REPLACE FUNCTION kcts(__start_time TIMESTAMPTZ, __oid BIGINT) RETURNS text AS $_$
71 BEGIN
72 RETURN to_char(__start_time, 'YYYY-MM-DD') || '/' || __oid;
73 END;
74$_$ Language 'plpgsql' IMMUTABLE STRICT;
75
76/**
77 Returns multiple rows, being the expanded rows for every map found by the passed in inputs.
78 Indexed by: result_id:map_name:doctype:pop:psource
79
80 Call with -- result_id, map_name, doctype, pop, psource
81 Everything from map_name down can be null.
82
83 Return map_name doctype pop psource key value list
84*/
85
86DROP FUNCTION kcx(text, text, text);
87CREATE OR REPLACE FUNCTION kcx(text, text, text)
88 RETURNS SETOF kc_row
89 AS 'libpg_kc', 'kc_expand'
90 LANGUAGE C STABLE STRICT;
91
92DROP FUNCTION kcx(text, text, text, text);
93CREATE OR REPLACE FUNCTION kcx(IN text, IN text, IN text, IN text)
94 RETURNS SETOF kc_row
95 AS 'libpg_kc', 'kc_expand'
96 LANGUAGE C STABLE STRICT;
97
98DROP FUNCTION kcx(text, text, text, text, text);
99CREATE OR REPLACE FUNCTION kcx(IN text, IN text, IN text, IN text, IN text)
100 RETURNS SETOF kc_row
101 AS 'libpg_kc', 'kc_expand'
102 LANGUAGE C STABLE STRICT;
103
104DROP FUNCTION kcx(text, text, text, text, text, text);
105CREATE OR REPLACE FUNCTION kcx(IN text, IN text, IN text, IN text, IN text, IN text)
106 RETURNS SETOF kc_row
107 AS 'libpg_kc', 'kc_expand'
108 LANGUAGE C STABLE STRICT;
109
110DROP FUNCTION kcx(text, text, text, text, text, text, text);
111CREATE OR REPLACE FUNCTION kcx(IN text, IN text, IN text, IN text, IN text, IN text, IN text)
112 RETURNS SETOF kc_row
113 AS 'libpg_kc', 'kc_expand'
114 LANGUAGE C STABLE STRICT;
115
116/**
117 Given the fixed values, writes (overrites) the kc entry.
118 map_name, start_time, new rid, array of rids, class, doctype, pop, psource
119 returns new kcid.
120*/
121DROP FUNCTION kcs (text, text, text, text array, text, text, text, text);
122CREATE FUNCTION kcs (text, text, text, text array, text, text, text, text)
123 RETURNS text
124 AS 'libpg_kc', 'kc_shrink'
125 LANGUAGE C STABLE STRICT;
126
127/**
128 Also need a way to delete values in the kcdb
129*/
130DROP FUNCTION kcd (text, text, text array);
131CREATE FUNCTION kcd (text, text, text array)
132 RETURNS bigint
133 AS 'libpg_kc', 'kc_delete'
134 LANGUAGE C STABLE STRICT;