1 files changed, 37 insertions, 0 deletions
diff --git a/example/bugs.sql b/example/bugs.sql
new file mode 100644
index 0000000..033a1f7
--- /dev/null
+++ b/example/bugs.sql
@@ -0,0 +1,37 @@
+-- pgcrypto is needed for UUIDs.
+create extension pgcrypto;
+
+-- A table with defaults and constraints.
+create table bugs (
+  id uuid not null primary key default gen_random_uuid(),
+  reported timestamp with time zone not null default now(),
+  reporter varchar not null default current_user,
+  project varchar(256) not null check (project <> ''),
+  description varchar(10240) not null check (description <> '')
+);
+
+-- No indexes, since they are irrelevant to this example (but normally
+-- they should be there).
+
+-- Additional restrictions.
+create policy bugs_select_policy on bugs for select using (true);
+create policy bugs_insert_policy on bugs for insert
+  with check (reported = now() and reporter = current_user);
+-- Update and delete policies can be added later.
+alter table bugs enable row level security;
+
+-- A search function for convenience.
+create or replace function bug_search (proj varchar, descr varchar, lim int, offs int)
+returns xml
+as $$
+  select query_to_xml(
+    'select id, date_trunc(''second'', reported) as reported, reporter, '
+    || 'project, substring(description from ''[^\n\r]+'') as summary from bugs '
+    || 'where (project like ' || quote_literal('%' || proj || '%')
+    || ') and (description like ' || quote_literal('%' || descr || '%')
+    || ') order by reported desc limit ' || lim || ' offset ' || offs,
+    false, false, 'bugs')
+$$ language sql;
+
+-- Now users can be added with select and/or insert privileges,
+-- including a guest user for unauthenticated requests.