💻
Today I Learned
  • README
  • analytics
    • convert json perline to panads data frame
    • hierarchical data format
    • pandas format custom date in data frame
  • bash
    • bash forloop
    • parameter expansion
    • prompt confirmation in bash
  • db
    • disabling foreign key when importing dump
    • postgres add object to jsonb array from the same column
    • postgres audit log trigger
    • postgres naming trigger
    • postgres rename enum
    • postgres reset sequence after import
    • postgres skip table from restore
    • set a column with value from different table
  • dgraph
    • dgraph docker compose whitelist ip
  • docker
    • docker compose make sure postgres is ready before starting other service
  • fish
    • edit last command in editor
  • git
    • checkout last branch
    • different between two dots and three
    • force fail commit on master
    • git finish helper script delete current branch and update master
    • git mergetool and diff with p4merge
    • global gitignore file
    • intellij as diff and mergetool
    • push only current branch
    • reset email multiple commit
    • sign commit with pgp
    • worktree switching branch without stash
  • go
    • default math.rand.source is thread save while rand.new source is not
    • deploying go to a vpn ubuntu server with github and ssl
  • k8s
    • copy file to pod
    • getting cpu and memory usage for container
    • how cert manager and letsencrypt challenge works
    • kubernetes resource short name
    • scale deployment
    • view secret
  • linux
    • boot zfs root filesystem on grub
    • clearing up swap space
    • connect bluetooth device from cli
    • get full argument from a process
    • merge multiple pdf into a single file
    • removing old kernel
    • symbolic vs hard link
    • zfs auto snapshot
    • zfs external backup drive with snapshot and encryption
  • net
    • dnssec
    • html form submit to different action depending on properties
    • ldap list users
  • osx
    • checksum a file from a url
    • cluster ssh in iterm2 with i2cssh
    • list open port
    • manage clipboard easily with jumpcut
    • pipe output to clipboard with pbcopy and pbpaste
    • show hidden file
    • sign application with self certificate
    • starting program on startup with login items
  • python
    • count frequency with lambda
    • dijkstra algorithm shortest path
    • double slash arithmetic operator
    • min and max of dict values
    • python3 match case
    • reduce and opeator
  • react
    • react named export vs default export
    • react useeffect
    • react.useref
  • unix
    • bulk renaming multiple file
    • convert pdf to text using ocr
    • diff output of 2 command
    • encryption with gpg
    • extend letsencrypt certificate with dns challenge
    • ffmpeg monitor and restart stream when it hung or stall
    • file size older than x days
    • filtering json with jq
    • find lines that matches on 2 different sorted file
    • find out what is using swap
    • fish environment variables from 1password
    • fix gpg warning unsafe permissions on homedir
    • formatting or parse json in command line
    • get all line except n last one
    • grep print only matched
    • grep using input file as pattern to search other file
    • jq counting lenght of an array
    • jq extracting properties to arrays from json row line
    • keep n recent item in folder
    • open last command in the editor with fc
    • parsing epoch timestamp to date
    • pbcopy alternative for copying to clipboard
    • process pipe operator
    • record a web stream to youtube
    • regex for validating password
    • rename tmux window
    • repeat content of text x time
    • replacing last command and execute it
    • reusing last command argument
    • send slack message from command line
    • sending curl post with file
    • sort file inline
    • specify compression level in tar gzip
    • zsh ctrl p same behavior as up arrow
  • vim
    • paste yanked text on command buffer
  • web
    • this article is published to dev to with github action
Powered by GitBook
On this page

Was this helpful?

  1. db

postgres audit log trigger

This triggers will log before and after fields for a table in JSON format.

CREATE TABLE changelog
(
    id         serial NOT NULL,
    ts         timestamp DEFAULT NOW(),
    table_name text,
    operation  text,
    new_val    json,
    old_val    json
);

CREATE INDEX changelog_ts_table_name_index
    ON changelog (ts DESC, table_name ASC);

CREATE OR REPLACE FUNCTION logchange() RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT'
    THEN
        INSERT INTO changelog (table_name, operation, new_val)
        VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE'
    THEN
        INSERT INTO changelog (table_name, operation, new_val, old_val)
        VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(NEW), ROW_TO_JSON(OLD));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
        INSERT INTO changelog
            (table_name, operation, old_val)
        VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER users_changelog_biudt
    BEFORE INSERT OR UPDATE OR DELETE
    ON users
    FOR EACH ROW
EXECUTE PROCEDURE logchange();
Previouspostgres add object to jsonb array from the same columnNextpostgres naming trigger

Last updated 3 years ago

Was this helpful?