PostgreSQL

PostgreSQL Snippets

PostgreSQL Extensions

PostgreSQL Row-Level Security

PostgreSQL Docker

PostgreSQL Tree Types

PostgreSQL Audit

PostgresSQL Foreign Data

Awesome List

https://github.com/dhamaniasad/awesome-postgres

Cheat Sheet & Quick Start

https://www.graphile.org/postgraphile/postgresql-schema-design/

Postgresql Cheat Sheet

Extensions

• https://www.pipelinedb.com/

PostgreSQL Replication, Redundancy & Backup

PL/Python vs PL/pgsql

https://www.postgresql.org/docs/11/plpython.html

The writer of a function in untrusted PL/Python must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator.

Row Level Security (RLS)

• https://www.postgresql.org/docs/11/ddl-rowsecurity.html
• https://medium.com/@cazzer/designing-the-most-performant-row-level-security-strategy-in-postgres-a06084f31945
• https://blog.2ndquadrant.com/application-users-vs-row-level-security/

Quick Commands

Using variables

WITH myconstants (var1, var2) as (
   values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
   OR something_else = var2;

Random int between 1 & 100

SELECT floor(random() * (100-1+1) + 1)::int;

Function

CREATE OR REPLACE FUNCTION random_between(low INT ,high INT) 
   RETURNS INT AS
$$
BEGIN
   RETURN floor(random()* (high-low + 1) + low);
END;
$$ language 'plpgsql' STRICT;

use

SELECT random_between(1,100);

---

Complicated example

add temp table

CREATE TEMP TABLE usr (id int, dummy text);
INSERT INTO usr VALUES
 (1,'foo')
,(2,'bar')
,(3,'baz')
,(4,'bee');

Function: ``` CREATE OR REPLACE FUNCTION x.foo()

 RETURNS VOID AS

$BODY$ DECLARE

   _r          record;
   _dummy      integer := 0;
   _ct         integer := 0;
   _5mod_ct    integer;

BEGIN

   RAISE INFO 'Start... ';
   FOR _r IN
       SELECT * FROM usr WHERE id < 50 LIMIT 10
   LOOP
       LOOP
           UPDATE usr SET dummy = 'foo' WHERE id = _r.id;

        _ct      := _ct + 1;
        _5mod_ct := 5 % _ct;

        EXIT WHEN _5mod_ct > 0; -- make sure this will be TRUE eventually!
        RAISE INFO '_5mod_ct = 0; repeating UPDATE!';
    END LOOP;

    _dummy := 10 / _5mod_ct;
    RAISE NOTICE '_5mod_ct: %, _dummy: %, user.id: %',
                  _5mod_ct, _dummy, _r.id;
END LOOP;
RAISE INFO 'Finished.';

END; $BODY$ LANGUAGE plpgsql;```

Testing

pgbench

https://www.2ndquadrant.com/en/blog/pg-phriday-postgres-zfs/