Skip to content

Instantly share code, notes, and snippets.

@migurski migurski/brooklynt.sql
Last active Jul 26, 2018

Embed
What would you like to do?
Use Brooklyn Integers directly in Postgres
--
-- Table of available integers with a boolean column for when they're used up.
--
DROP TABLE IF EXISTS brooklyn_integers;
CREATE TABLE brooklyn_integers (used BOOLEAN DEFAULT false NOT NULL, value BIGINT NOT NULL);
CREATE INDEX brooklyn_integers_used ON brooklyn_integers (used);
CREATE UNIQUE INDEX brooklyn_integers_value ON brooklyn_integers (value);
--
-- Request a single unused integer and mark it used.
-- Returns null when none are available.
--
DROP FUNCTION IF EXISTS brooklynt ();
CREATE FUNCTION brooklynt () RETURNS BIGINT
LANGUAGE PLPGSQL
AS $$
DECLARE result BIGINT;
BEGIN
SELECT value INTO result FROM brooklyn_integers WHERE NOT used LIMIT 1;
UPDATE brooklyn_integers SET used = TRUE WHERE value = result;
RETURN result;
END
$$;
--
-- Populate brooklyn_integers table from brooklynintegers.com.
-- http://brooklynintegers.com/api/methods/brooklyn.integers.create
-- https://www.postgresql.org/docs/9.0/static/plpython-funcs.html
--
DROP FUNCTION IF EXISTS populate_brooklyn_integers (count INTEGER);
CREATE FUNCTION populate_brooklyn_integers (count INTEGER) RETURNS INTEGER
LANGUAGE plpython2u
AS $$
import json, httplib
conn = httplib.HTTPSConnection('api.brooklynintegers.com')
inserted = 0
for _ in range(count):
conn.request('POST', '/rest/', 'method=brooklyn.integers.create',
{'Content-Type': 'application/x-www-form-urlencoded'})
try:
value = json.load(conn.getresponse())['integers'][0]['integer']
plpy.execute("INSERT INTO brooklyn_integers (value) VALUES ({:d})".format(value))
plpy.info('Added integer {:d}'.format(value))
inserted += 1
except Exception as e:
plpy.warning('Failed to add one integer: {}'.format(e))
return inserted
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.