postgresql

The “Code Organization Problem”

Recently someone asked me how I organize my Rails apps. That question came unexpectedly, and I probably did a poor job explaining what I do – this is essentially something that can span multiple hours of discussion, and probably will bring lots of disagreements. So I decided to make a post, and hopefully this might help other people (and to be fair, this is not only valid for Ruby but for any language, really).

First things first, if you I am working in a project with multiple people, I will try to keep the project’s defaults; if the project already have specific organizations (like “app/services” or “app/presenters”) I will follow them; Rails is probably one of the best experiences I have ever seen in collaborating with other teams, and eroding this might bring down the biggest benefit of the framework. But considering I don’t have an already existing structure, or if I need to create some new ones, I will try to bring some personal rules:
(more…)

By Maurício Szabo, ago

Advent of Code in SQL – Day 01

Ok, this year I decided to try the Advent of Code. But to level up my SQL game, I decided to do it purely in PostgreSQL. No stored procedures, no tables, just pure logic over SQL (or, at least, PostgreSQL-flavored SQL). I’ll also try to not use PG specific datatypes like JSONB or Arrays/Ranges, but I can’t promise this – the challenge is already hard with the way it is.

So, I decided that the input will be a single table called input with a single record and field, called input. This means that I have to split the input in some way – I’m currently using regexp_split_to_table(input.input, '\n') to generate a table with all the lines when this makes sense.

So, day 1. The first thing is to generate the input:

WITH input AS (SELECT '199
200
208
210
200
207
240
269
260
263' AS input)

The Day 01 problem is kinda simple: you compare a row with the next one and when the next one is greater, you add 1 to a result. So in this case, we do need to split the input into numeric rows. There are two ways of doing this: you either first split then cast, or split and cast in a single query. The first one is easier, so let’s go with that:

WITH depths_as_str as (SELECT regexp_split_to_table(input.input, '\n') depth FROM input)
  , depths as (SELECT depth  :: integer FROM depths_as_str)

(more…)

By Maurício Szabo, ago