commit e66488e9bebb11b564df22beebb4506d90e613a9
parent 9ee2dab675340b6426c85e9c9b3fd92bf032c2b6
Author: ling0x <ling0x@users.noreply.github.com>
Date: Fri, 12 Jun 2026 15:09:16 +0100
gawk
Diffstat:
| A | commands/gawk.md | | | 115 | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
1 file changed, 115 insertions(+), 0 deletions(-)
diff --git a/commands/gawk.md b/commands/gawk.md
@@ -0,0 +1,115 @@
+# ================================================================================ GAWK — quick notes (Arch Linux)
+
+GNU Awk. Handy for shaping psql or other CLI output in a pipe.
+
+## INSTALL
+
+sudo pacman -S gawk postgresql
+
+psql is in the postgresql package.
+
+## PSQL: OUTPUT THAT PIPES WELL
+
+Use unaligned, one-field-per-line rows:
+
+psql -h localhost -U myuser -d mydb -t -A -F','\
+-c "SELECT id, name, active FROM users LIMIT 5"
+
+Flags: -t rows only (no header/footer) -A unaligned -F',' field separator (comma
+or tab)
+
+## SIMPLE EXAMPLES
+
+Print one column
+
+```
+ psql -h localhost -U myuser -d mydb -t -A -c "SELECT email FROM users" \
+ | gawk '{ print $1 }'
+
+ With comma-separated psql output:
+
+ psql ... -t -A -F',' -c "SELECT id, email FROM users" \
+ | gawk -F',' '{ print $2 }'
+
+
+Filter rows
+~~~~~~~~~~~
+
+ Keep rows where active (3rd field) is true:
+
+ psql ... -t -A -F',' -c "SELECT id, name, active FROM users" \
+ | gawk -F',' '$3 == "t" { print $1, $2 }'
+
+
+Add a CSV header
+```
+
+psql ... -t -A -F',' -c "SELECT id, name FROM users"\
+| gawk -F',' 'BEGIN { print "id,name" } { print }'
+
+Sum a numeric column
+
+```
+ psql ... -t -A -c "SELECT amount FROM orders" \
+ | gawk '{ sum += $1 } END { print sum }'
+
+
+key=value lines
+~~~~~~~~~~~~~~~
+
+ psql ... -t -A -F',' -c "SELECT id, name FROM users WHERE id = 42" \
+ | gawk -F',' '{ printf "id=%s name=%s\n", $1, $2 }'
+
+
+COMPLEX EXAMPLE — MULTI-RECORD CLI OUTPUT
+-----------------------------------------
+
+When input is not one row per line (nested structs, repeated fields):
+
+ - set RS (record separator) to split on blocks, not newlines
+ - use gawk match() with a 3rd arg for capture groups (needs gawk, not awk)
+ - loop with match() to collect repeated fields (e.g. roles)
+
+Turn verbose get-all-profiles output into a table:
+
+ kioko cad "$ENV" db user get-all-profiles | gawk '
+ BEGIN {
+ RS = "UserProfile"
+ printf "%-3s %-22s %-20s %-8s %-5s %s\n", "ID", "USERNAME", "NAME", "BLOCKED", "LINK", "ROLES"
+ }
+ /TrimmedUser/ {
+ match($0, /id: ([0-9]+)/, a)
+ match($0, /username: "([^"]*)"/, u)
+ match($0, /first_name: "([^"]*)"/, f)
+ match($0, /last_name: "([^"]*)"/, l)
+ match($0, /blocked: ([a-z]+)/, b)
+ match($0, /email_link: ([0-9]+)/, e)
+ roles = ""
+ s = $0
+ while (match(s, /role: ([A-Za-z]+)/, r)) {
+ roles = roles (roles ? "," : "") r[1]
+ s = substr(s, RSTART + RLENGTH)
+ }
+ printf "%-3s %-22s %-20s %-8s %-5s %s\n", a[1], u[1], f[1] " " l[1], b[1], e[1], roles
+ }'
+
+ What each piece does:
+
+ RS = "UserProfile" split stream on each profile block
+ /TrimmedUser/ only process records with that marker
+ match($0, /.../, arr) put capture group 1 into arr[1]
+ while (match(s, ...)) walk the string; collect every role:
+ printf "%-3s ..." fixed-width columns
+
+ Same idea works on any verbose tool output, or psql nested text
+ (e.g. json_agg) when flat -F splitting is not enough.
+
+
+TIPS
+----
+
+ * Match gawk -F to the separator you pass to psql -F.
+ * Use gawk (not plain awk) for match(str, regex, array) captures.
+ * Quote SQL in -c "..." so the shell does not eat $ in awk patterns.
+ * In psql, \copy and \o are fine interactively; gawk fits scripts and pipes.
+```
