Skip to content

Instantly share code, notes, and snippets.

View vincentdesmares's full-sized avatar
✍️
Preparing my next talk

Vincent vincentdesmares

✍️
Preparing my next talk
View GitHub Profile
@vincentdesmares
vincentdesmares / gist:c360e5d26e097ef5b4b1
Created March 15, 2016 06:34
A redux state tree example
{
house : [{
id: 1,
name: test',
rooms: [{
id:'a',
name: 'room a',
entities: [{
id: 'x',
name: 'No more examples',
@vincentdesmares
vincentdesmares / test.js
Created March 15, 2016 06:36
Recursive reducers around a main reducer?
combineReducers({
house: wrapReducerProperties(
rooms: wrapReducerProperties({
entities: wrapReducerProperties({
foo:fooReducer,
test:testReducer
},
entitiesReducer)
),
houseReducer
-- Create the master table tablename_with_partition:
-- You should use the same exact schema as the one from the non partitioned table.
CREATE TABLE tablename_with_partition
(
id integer NOT NULL DEFAULT nextval('tablename'::regclass),
"name" character varying(150) NOT NULL,
description text NOT NULL,
period character varying(10) NOT NULL
CONSTRAINT tablename_with_partition_pkey PRIMARY KEY (id)
) WITH ( OIDS=FALSE);
-- Attach a magic function to the insert of this table:
CREATE OR REPLACE FUNCTION create_partition_and_insert()
RETURNS TRIGGER AS
$BODY$
DECLARE
partition VARCHAR(25);
BEGIN
partition := TG_RELNAME || '_' || NEW.period || ‘p’;
IF NOT EXISTS(SELECT relname
FROM pg_class
CREATE TRIGGER tablename_insert_trigger
BEFORE INSERT ON tablename_wtih_partition
FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();
-- Copy data from the non partitioned to the partitioned version:
INSERT INTO tablename_with_partition SELECT * tablename;
CREATE OR REPLACE FUNCTION run_on_partitions(TEXT, TEXT)
RETURNS INTEGER AS $$ DECLARE partition RECORD;
tablename TEXT = $1;
sql TEXT = $2;
sqlReplaced TEXT;
BEGIN tablename := tablename || '%p';
FOR partition IN SELECT relname :: TEXT AS rel
FROM pg_class
WHERE relname :: TEXT LIKE tablename AND relkind = 't'
ORDER BY relname LOOP sqlReplaced := replace(sql, '', partition.rel);
7 - SELECT
1 - [ FROM ... ]
2 - [ WHERE ... ]
3 - [ GROUP BY ... ]
4 - [ HAVING ... ]
5 - WINDOW window_name AS (
[ PARTITION BY ... ]
[ ORDER BY ... ]
[ RANGE ... ]
)
SELECT
*,
min(salary)
OVER report_by_category,
avg(salary)
OVER report_by_category,
max(salary)
OVER report_by_category
FROM employe
WINDOW report_by_category AS (
id | name | salary | category | min | avg | max
-----+-------------------+--------+----------+------+------------------+------
1003 | Employe name 1003 | 6515 | C | 1009 | 5550.74427480916 | 9996
1005 | Employe name 1005 | 8187 | C | 1009 | 5550.74427480916 | 9996
1001 | Employe name 1001 | 6106 | D | 1001 | 5507.41912512716 | 9976
1002 | Employe name 1002 | 2491 | E | 1001 | 5551.63221884498 | 9997
1004 | Employe name 1004 | 5130 | E | 1001 | 5551.63221884498 | 9997