Tag Archives: sql

PostgreSQL and Row IDs

From a blog post [PostgreSQL] allows for treating INSERT and UPDATE statements as table references, when used with the RETURNING clause. This is quite fancy, even if not very useful in everyday SQL. “not useful”? Madness I tell you I respectfully disagree. I only discovered RETURNING in the last year; and it’s incredibly useful in… Read More »

Recursive SQL Query

Imagine these tables (I’m using PostgreSQL): CREATE Capabilities ( ID serial PRIMARY KEY, Code text NOT NULL ); CREATE CapabilityPermissions ( ID serial PRIMARY KEY, CapabilitityID integer NOT NULL REFERENCES Capabilities(ID), PermissionID integer NOT NULL REFERENCES Capabilities(ID) ); What I’m aiming for here is the ability to make a permissions hierarchy. This will let us… Read More »