bitner

bitner

bitner

/*
    This create function script must be run as a user that owns the collections table!
    By setting SECURITY DEFINER on the function, we are telling Postgres that this function will run
    with the permissions of the user that created the function rather than the user that is calling the function.

    Because this does add a bit of security risk, we take a bit of extra precaution and schema qualify
    any tables that we are referencing and set the SEARCH_PATH to only explicity include pg_catalog and pg_temp schemas.

    By running the function as the user that owns the collections table, we are able to enable and disable the triggers that are set on
    that table.

bitner

-- Make sure that you do all of this in your own schema
-- Postgres has a search path where it is always going to create new things (tables, functions, etc) in the first entry that exists in your search_path
SHOW search_path;

-- pgstac lives in the pgstac schema, to use pgstac without needing to prefix everything
-- you always want to have public in your search_path as that is where all the main things in the database are (including postgis - think of it like /usr/bin)
SET SEARCH_PATH to pgstac, public;

-- Create your own schema to play with these things
CREATE SCHEMA playground;

bitner

-- Function to take an x, y, z and turn it into a quadkey text string
CREATE OR REPLACE FUNCTION quadkey(zoom int, tx int, ty int) RETURNS text AS $$
DECLARE
i int;
digit int;
quadkey text := '';
mask int;
BEGIN
FOR i IN REVERSE zoom..1 LOOP
    digit := 0;

bitner

import psycopg
import os
import time

# Get a an iterator to use for sample of half million ids cached to a file
def sample_ids():
    file='sampleids.csv'
    if not os.path.exists(file):
        with open(file, 'wb') as f:
            with psycopg.connect(os.environ.get('PGURL')) as conn:

bitner

\set ON_ERROR_STOP ON
BEGIN;
DROP SCHEMA IF EXISTS anthony_test CASCADE;
CREATE SCHEMA anthony_test;
SET SEARCH_PATH to anthony_test, public;

CREATE TABLE downloads(
    id bigint GENERATED ALWAYS AS IDENTITY primary key,
    created_at timestamptz NOT NULL DEFAULT now(),
    username text NOT NULL,

bitner

‎‎​

bitner

‎‎​

bitner

#!/bin/bash
psql <<EOD
    SET SEARCH_PATH to public, data;
    DROP VIEW IF EXISTS items_as_ndjson;
    CREATE OR REPLACE VIEW items_as_ndjson AS
    SELECT
        date_trunc('month', datetime)::date as month,
        datetime,
        jsonb_build_object(
            'id', items.id,