Skip to content

Instantly share code, notes, and snippets.


Florian Klein docteurklein

View GitHub Profile
docteurklein / traverse.sql
Last active Sep 20, 2021
postgres query to generate links based on relations
View traverse.sql
drop function if exists explore(text);
create function explore(sql text) returns setof record
language 'plpgsql' as $$
return query execute format($sql$
with node (tableoid, row) as (
docteurklein / dynamic.sql
Created Jan 18, 2021
postgres dynamic sql
View dynamic.sql
drop function if exists exec(text, text[]);
create function exec(sql text, params text[] default '{}') returns void
language 'plpgsql' as $$
execute format(sql, variadic params);
-- select exec('select 1');
drop function if exists query(text, text[]);
View test.sql
load 'age';
SET search_path = ag_catalog, "$user", public;
create table if not exists history (year, event) as values (1996, 'postgresql'), (2016, 'agensgraph');
select drop_graph('g', true);
select create_graph('g');
select * from cypher('g', $$
create (:dev {name: 'someone', year: 2015})

declarative DDL


Define your database schema in a declarative way, by providing the wanted state, and this tool will calculate a diff to reconcile it with reality.


Let's imagine a scenario:

docteurklein /
Last active May 7, 2020
Work on a cabal dependency to contribute upstream

Work on a cabal dependency to contribute upstream

The haskell dependency management can be quite intimidating at first, but after a while you get used to it and realize it's very similar to rust's cargo (so many things are similar between rust and haskell, by the way).

One of the things I found particularly easy (once you know how) is to edit a dependency locally to work on it.

So imagine you have a cabal project with a dependency:

View dbal-mig.php
use Doctrine\DBAL\Schema\Comparator;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;
$connFrom = DriverManager::getConnection([
'url' => "mysql://root:root@",
], new Configuration);
$connTo = DriverManager::getConnection([
docteurklein / 0_setup.sql
Last active Jan 14, 2020
psql -v ON_ERROR_STOP=1 -c begin $(find . -name '*.sql' -printf ' -f %f\n' | sort -V | xargs) -c commit
View 0_setup.sql
create table if not exists migration (
id bigint primary key,
at timestamptz not null default clock_timestamp()
docteurklein / migrate.sql
Created Jan 13, 2020
postgres-only transactional migration script
View migrate.sql
create table if not exists migration (id bigint primary key, at timestamptz not null default clock_timestamp());
drop function if exists migrate;
create function migrate() returns bigint language plpgsql as $$
declare current_migration bigint;
select coalesce((select id from migration order by id desc limit 1), 0) into current_migration;
View gist:868dcd62c86d95e688130fea09f0e45a
create function es.project(event returns void
language plpgsql as $$
case event.type
when 'user_registered' then
insert into es.active_users
(user_id , name , sha256 , updated_at) values
(event.aggregate_id , event.payload->>'name' , event.payload->>'sha256' , event.added_at);
when 'user_changed_password' then
update es.active_users set
error[E0599]: no method named `map` found for type `postgres::notification::BlockingIter<'_>` in the current scope
  --> src/
24 ||notification| {
   |                   ^^^
   = note: the method `map` exists but the following trait bounds were not satisfied:
           `&mut postgres::notification::BlockingIter<'_> : fallible_iterator::FallibleIterator`
 `&amp;mut postgres::notification::BlockingIter&lt;'_&gt; : std::iter::Iterator`