sql_tdwtf

#!/bin/bash

DATABASE=${DATABASE:-discourse}
LIMIT=${LIMIT:-25}
NAME=${NAME:-%}

function sql(){
    psql -d $DATABASE -e -c "$@
" | sed 1d
    return
}

function restore(){ # restore a backup from /tmp
    previous_backup=""
    most_recent_backup=$(basename $(ls /tmp/*-20??-??-??-??????.tar.gz -rt | tail -n1))
    if [ -e ~/.${DATABASE}_backup ]; then
        previous_backup=$(basename $(head -n1 ~/.${DATABASE}_backup))
    fi
    if [ "$most_recent_backup" == "$previous_backup" ]; then
        echo "Already using the most recent backup available: $previous_backup"
        exit 0
    fi
    backup_date=$(echo "$most_recent_backup" | sed -e 's/^.*\(20[0-9][0-9].*\)-.......tar.gz/\1/g')

    echo "$most_recent_backup - restore? [Y/n]"
    read yn
    if [[ $yn == "Y" || $yn == "y" || $yn = "" ]]; then
        echo "Restoring..."
    else
        exit 0
    fi

    mkdir -p ~/restores/$backup_date
    echo "Extracting $most_recent_backup"
    tar -C ~/restores/$backup_date -xf /tmp/$most_recent_backup dump.sql
    echo "Restoring..."
    psql -d $DATABASE -c "DROP SCHEMA backup CASCADE"
    psql $DATABASE < ~/restores/$backup_date/dump.sql
    psql -d $DATABASE -c "ALTER SCHEMA public RENAME TO backup;"
    psql -d $DATABASE -c "ALTER SCHEMA restore RENAME TO public"

    echo "$most_recent_backup" > ~/.${DATABASE}_backup

}


function backup_date(){ # When the backup was taken
    sql "SELECT created_at
FROM post_actions
ORDER BY created_at DESC
LIMIT 1" | grep "201[45]-"
}

function most_liked_posts_x_1000(){ # Most liked posts outside of /t/1000
    sql "
SELECT row_number() OVER (ORDER BY like_count DESC) n,
   badge_posts.topic_id topic,
   badge_posts.post_number post,
   badge_posts.like_count likes,
   users.username
FROM badge_posts, users
WHERE topic_id != 1000 AND
   badge_posts.user_id = users.id
ORDER BY like_count DESC
LIMIT $LIMIT"
}

function most_liked_topics(){ # Most liked topic starters
    sql "
SELECT row_number() OVER (ORDER BY bp.like_count DESC) n,
   bp.topic_id topic,
   bp.post_number post,
   bp.like_count likes,
   u.username,
   t.title
FROM badge_posts bp
JOIN topics t on t.id = bp.topic_id
JOIN users u on u.id = bp.user_id
WHERE bp.post_number = 1
ORDER BY bp.like_count DESC
LIMIT $LIMIT"
}



function most_liked_users_x_1000(){ # Most liked users outside of /t/1000
    sql "
SELECT row_number() OVER (ORDER BY SUM(badge_posts.like_count) DESC) n,
   SUM(badge_posts.like_count) likes,
   users.username
FROM badge_posts, users
WHERE topic_id != 1000 AND
   badge_posts.user_id = users.id
GROUP BY users.username
ORDER BY SUM(badge_posts.like_count) DESC
LIMIT $LIMIT"
}

function total_likes_x_1000(){ # Total likes excluding /t/1000
    sql "
SELECT row_number() OVER (ORDER BY SUM(badge_posts.like_count) DESC) n,
   SUM(badge_posts.like_count) likes,
   users.username
FROM badge_posts, users
WHERE topic_id != 1000 AND
   badge_posts.user_id = users.id
GROUP BY users.username
ORDER BY SUM(badge_posts.like_count) DESC
LIMIT $LIMIT"
}


function posts_days(){ # Posts read and days visited
    sql "
SELECT row_number() OVER (ORDER BY sum(posts_read) DESC) as n,
   sum(posts_read) as \"Posts read\",
   count(*) as \"Days visited\",
   u.username as \"User id\"
FROM user_visits v
JOIN users u on u.id = v.user_id
GROUP BY u.username
ORDER BY sum(posts_read) DESC
LIMIT $LIMIT"
}


function days(){ # Days visited
    sql "
SELECT count(*) as \"Days visited\",
   u.username as \"User id\"
FROM user_visits v
JOIN users u on u.id = v.user_id
GROUP BY u.username
ORDER BY count(*) DESC
LIMIT $LIMIT"
}


function posts_read(){ # Number of publicly visible (?)posts read
    sql "
SELECT COUNT(1), u.username
FROM badge_posts bp
JOIN post_timings pt ON
   pt.topic_id = bp.topic_id AND
   pt.post_number = bp.post_number
JOIN users u ON u.id = pt.user_id
GROUP BY u.username
ORDER BY COUNT(*) DESC
LIMIT $LIMIT"
}

function posts_read2(){ # Number of publicly visible(?) posts read
    sql "
SELECT count(1)
FROM posts p
JOIN post_timings pt ON
   pt.topic_id = p.topic_id AND
   pt.post_number = p.post_number
LIMIT $LIMIT"
}

function posts_read3(){ # Number of publicly visible(?) posts read
    sql "
SELECT us.posts_read_count, u.username
FROM user_stats us
JOIN users u on u.id=us.user_id
ORDER BY us.posts_read_count DESC
LIMIT $LIMIT"
}

function posts_read4(){ # @Matches variant
    sql "
SELECT count(1)
FROM
(select p.topic_id, p.post_number from posts as p inner join post_timings as pt on pt.topic_id = p.topic_id and pt.post_number = p.post_number) as a
LIMIT $LIMIT"
}

function likes_liked(){ # Who's liked who the most
    sql "
SELECT l.username Liker, count(*), r.username Liked
FROM post_actions pa
INNER JOIN users l ON l.id=pa.user_id
INNER JOIN posts p on p.id=pa.post_id
INNER JOIN users r on r.id=p.user_id
WHERE pa.post_action_type_id=2 AND
   p.topic_id != 1000
GROUP BY Liker, Liked
ORDER BY count(*) DESC
LIMIT $LIMIT"
}

function likes_per_day(){
    sql "
WITH p1000 AS (
    SELECT date_trunc('day',pa.created_at), COUNT(*)
    FROM posts p
    JOIN post_actions pa on p.id=pa.post_id
    WHERE pa.post_action_type_id=2 AND
        p.topic_id = 1000
    GROUP BY date_trunc('day',pa.created_at)
),
xp1000 AS (
    SELECT date_trunc('day',pa.created_at), COUNT(*)
    FROM posts p
    JOIN post_actions pa on p.id=pa.post_id
    WHERE pa.post_action_type_id=2 AND
        p.topic_id != 1000
    GROUP BY date_trunc('day',pa.created_at)
)
SELECT xp1000.date_trunc, p1000.count p1000, xp1000.count xp1000
FROM p1000
FULL JOIN xp1000 on xp1000.date_trunc = p1000.date_trunc"
}

function post_actions(){ # List post action types
    sql "
SELECT id, name_key as Action, is_flag as flag
FROM post_action_types
ORDER BY id asc"
}

function visits_100(){ # Number of visits in the past 100 days
    sql "
SELECT u.username, count(u.username)
FROM user_visits uv
INNER JOIN users u ON u.id=uv.user_id
WHERE date_trunc('day',uv.visited_at) >= date_trunc('day', now() - interval '99 day')
GROUP BY u.username
ORDER BY count(u.username) DESC
LIMIT $LIMIT"
}

function visits_100_count(){ # Number of people with X vists in the last 100 days
    sql "
SELECT count(out_of_100), out_of_100 FROM (
    SELECT u.username, count(u.username) out_of_100
    FROM user_visits uv
    INNER JOIN users u ON u.id=uv.user_id
    WHERE date_trunc('day',uv.visited_at) >= date_trunc('day', now() - interval '99 day')
    GROUP BY u.username
    ORDER BY count(u.username) DESC
    ) x
GROUP BY out_of_100
ORDER BY out_of_100 DESC
LIMIT $LIMIT"
}


function visits_100_named(){  # Number of visits in the past 100 days given a NAME
    sql "
SELECT u.username, count(u.username)
FROM user_visits uv
INNER JOIN users u ON u.id=uv.user_id
WHERE date_trunc('day',uv.visited_at) >= date_trunc('day', now() - interval '99 day') AND
    u.username = '$NAME'
GROUP BY u.username"
}

function boomzilla(){ # xx
    sql "
with consecutive as (
  select
    v1.user_id,
    v1.visited_at as start,
    v2.visited_at as end,
    count( v3.visited_at ) days
  from user_visits v1
  join user_visits v2 on (v2.user_id = v1.user_id)
    and (v1.visited_at < v2.visited_at)
  join user_visits v3 on (v3.user_id = v1.user_id)
    and (v3.visited_at between v1.visited_at and v2.visited_at)
  group by v1.user_id, v1.visited_at, v2.visited_at
  having count( v3.visited_at ) = (v2.visited_at - v1.visited_at + 1)
  )
select user_id, max(days)
from consecutive
group by user_id"
}

function attendance(){ # Record of continuous days of attendance
    sql "
WITH StartingPoints AS (
   SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
   FROM user_visits AS A
   WHERE NOT EXISTS (
      SELECT *
      FROM user_visits AS B
      WHERE B.visited_at = A.visited_at - 1 AND
      B.user_id = A.user_id
   )
),
EndingPoints AS (
   SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
   FROM user_visits AS A
   WHERE NOT EXISTS (
      SELECT *
      FROM user_visits AS B
      WHERE B.visited_at = A.visited_at + 1 AND
      B.user_id = A.user_id
   )
)
SELECT u.username, S.visited_at AS start_range, E.visited_at AS end_range, (E.visited_at - S.visited_at +1) AS Days
FROM StartingPoints AS S
JOIN EndingPoints AS E ON E.rownum = S.rownum
JOIN users u ON u.id=S.user_id AND
u.username like '$NAME'"
}

function max_attendance(){ # Most continuous days of attendance
    sql "
SELECT username, user_id, current_timestamp granted_at, max(days) from (
    WITH StartingPoints AS (
    SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
    FROM user_visits AS A
    WHERE NOT EXISTS (
        SELECT *
        FROM user_visits AS B
        WHERE B.visited_at = A.visited_at - 1 AND
        B.user_id = A.user_id
    )
    ),
    EndingPoints AS (
    SELECT user_id, visited_at, ROW_NUMBER() OVER(ORDER BY user_id, visited_at) AS rownum
    FROM user_visits AS A
    WHERE NOT EXISTS (
        SELECT *
        FROM user_visits AS B
        WHERE B.visited_at = A.visited_at + 1 AND
        B.user_id = A.user_id
    )
    )
    SELECT u.username, S.user_id AS user_id, S.visited_at AS start_range, E.visited_at AS end_range, (E.visited_at - S.visited_at +1) AS Days
    FROM StartingPoints AS S
    JOIN EndingPoints AS E ON E.rownum = S.rownum
    JOIN users u ON u.id=S.user_id
) x
GROUP BY x.username, x.user_id
HAVING max(days)>=100"
}

function 2n_posts(){ # How many eligible posts have been made
    sql "
WITH exclusions AS ( /* Which categories to exclude from counters */
        SELECT user_id, id, topic_id, post_number
        FROM posts
        WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
        user_id IN  (
                SELECT gu.user_id
                FROM group_users gu
                WHERE group_id IN(
                        SELECT g.id
                        FROM groups g
                        WHERE g.name IN ('admins')
                )
        )
)
SELECT u.username, count(*)
FROM badge_posts  bp
JOIN users u on u.id=bp.user_id AND u.username LIKE '$NAME'
WHERE topic_id NOT IN ( /* Topics with less than 10 posts */
        SELECT topic_id
        FROM badge_posts
        GROUP BY topic_id
        HAVING count(topic_id) < 10
) AND topic_id NOT IN ( /* Excluded topics */
        SELECT topic_id
        FROM exclusions
)
GROUP BY u.username
ORDER BY count(*) DESC
LIMIT $LIMIT"
}

function percent_31() { # 31% query
    sql "
SELECT u.username, user_id, 0 post_id, current_timestamp granted_at, count(user_id)
FROM user_visits uv
JOIN users u ON u.id = uv.user_id
WHERE date_trunc('day',uv.visited_at) >=
    date_trunc('day', now() - interval '31 day')
GROUP BY user_id, u.username
HAVING count(user_id) >= 31
"
}

function percent_100() { # 100% query
    sql "
SELECT u.username, user_id, 0 post_id, current_timestamp granted_at, count(user_id)
FROM user_visits uv
JOIN users u ON u.id = uv.user_id
WHERE date_trunc('day',uv.visited_at) >=
    date_trunc('day', now() - interval '100 day')
GROUP BY user_id, u.username
HAVING count(user_id) >= 100
"
}

function top5(){ # Top 5% posters in the last month
    sql "
WITH LastMonth AS (
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id
    WHERE topic_id NOT IN (
       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4
    ) AND topic_id NOT IN (
       1000, 1673, 3125
    ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    HAVING count(*) > 1
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
    SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number < TotalUsers.max *.05"
}

function month_posts_old(){ # Post counts per user over last month
    sql "
WITH LastMonth AS (
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id
    WHERE topic_id NOT IN (
       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4
    ) AND topic_id NOT IN (
       1000, 1673, 3125
    ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
    SELECT max(row_number) from LastMonth
)
SELECT row_number, u.username, count
FROM LastMonth, TotalUsers
LIMIT $LIMIT"
}

function month_posts(){ # post counts over last month
    sql "
WITH exclusions AS ( /* Which categories to exclude from counters */
    SELECT user_id, id, topic_id, post_number
    FROM posts
    WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
    user_id IN  (
        SELECT gu.user_id
        FROM group_users gu
        WHERE group_id IN(
            SELECT g.id
            FROM groups g
            WHERE g.name IN ('admins')
        )
    )
),
LastMonth AS ( /* Count eligible posts from last month */
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id AND
        bp.user_id NOT IN ( /* ignore bots */
                SELECT gu.user_id
                FROM group_users gu
                WHERE group_id IN(
                    SELECT g.id
                    FROM groups g
                    WHERE g.name IN ('bots')
                )
        )
    WHERE topic_id NOT IN ( /* short topics */
       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <10
    ) AND topic_id NOT IN ( /* Ineligible topics */
       SELECT topic_id
       FROM exclusions
    ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    HAVING count(*) > 1
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
    SELECT max(row_number) from LastMonth
),
QUERY AS (
SELECT username, user_id, row_number, cast(row_number*100.0/TotalUsers.max AS numeric(36,2)) as percent, count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE cast(row_number*100.0/TotalUsers.max AS numeric(36,2)) < 30
LIMIT $LIMIT)
SELECT row_number as rank, username, percent, count
FROM QUERY"
}

function excluded_cats(){ # Which categories are excluded from badges to prevent abuse
    # Any thread including a post by PJH including the text c09fa970-5a9a-11e4-8ed6-0800200c9a66 should be excluded
    sql "
WITH exclusions AS (
    SELECT p.user_id, p.id, topic_id, t.name, post_number
    FROM posts p
    JOIN topics t ON t.id=p.topic_id
    WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
    p.user_id IN  (
        SELECT gu.user_id
        FROM group_users gu
        WHERE group_id IN(
            SELECT g.id
            FROM groups g
            WHERE g.name IN ('admins')
        )
    )
)
SELECT * from exclusions"
}

function too_much_time(){ # Who's got this badge?
    sql "
WITH exclusions AS (
    SELECT user_id, id, topic_id, post_number
    FROM posts
    WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
    user_id IN  (
        SELECT gu.user_id
        FROM group_users gu
        WHERE group_id IN(
            SELECT g.id
            FROM groups g
            WHERE g.name IN ('admins')
        )
    )
),
LastMonth AS (
    SELECT row_number() OVER (ORDER BY count(*) DESC, bp.user_id), u.username, bp.user_id, count(*)
    FROM badge_posts bp
    JOIN users u on u.id=bp.user_id AND
        bp.user_id NOT IN (
                SELECT gu.user_id
                FROM group_users gu
                WHERE group_id IN(
                    SELECT g.id
                    FROM groups g
                    WHERE g.name IN ('bots')
                )
        )
    WHERE topic_id NOT IN (
       SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4
    ) AND topic_id NOT IN (
       SELECT topic_id
       FROM exclusions
    ) AND bp.created_at > CURRENT_DATE - INTERVAL '1 month'
    GROUP BY u.username, bp.user_id
    HAVING count(*) > 1
    ORDER BY count(*) DESC, bp.user_id
),
TotalUsers AS (
    SELECT max(row_number) from LastMonth
)
SELECT username, user_id, row_number, count, CURRENT_DATE granted_at
FROM LastMonth, TotalUsers
WHERE row_number = 1"
}

POW=${POW:-4}
function posts_2_static() { # 2^0 posts testing
    sql "
SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE topic_id NOT IN (
   SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4
) AND topic_id NOT IN (
   1000, 1673, 3125
) GROUP BY user_id HAVING count(*) >= pow(2, $POW)
"
}

function posts_2_dynamic() { # 2^0 posts testing
    sql "
WITH exclusions AS (
    SELECT user_id, id, topic_id, post_number
    FROM posts
    WHERE raw LIKE '%c09fa970-5a9a-11e4-8ed6-0800200c9a66%' AND
    user_id IN  (
        SELECT gu.user_id
        FROM group_users gu
        WHERE group_id IN(
            SELECT g.id
            FROM groups g
            WHERE g.name IN ('admins')
        )
    )
)
SELECT user_id, 0 post_id, current_timestamp granted_at
FROM badge_posts
WHERE topic_id NOT IN (
   SELECT topic_id FROM badge_posts GROUP BY topic_id HAVING count(topic_id) <4
) AND topic_id NOT IN (
   SELECT topic_id FROM exclusions
) GROUP BY user_id HAVING count(*) >= pow(2, $POW)
"
}

function pedant(){ # Pedantry UUID given
    sql "
SELECT *
FROM post_revisions
WHERE modifications like '%- |-%3b27ba70-5ab4-11e4-8ed6-0800200c9a66%cooked:%'
AND user_id IN  (
    SELECT gu.user_id
    FROM group_users gu
    WHERE group_id IN(
        SELECT g.id
        FROM groups g
        WHERE g.name IN ('admins')
    )
)
"
}

function posts_by_hours(){
    sql "
SELECT DATE_TRUNC('day', created_at), EXTRACT(hour FROM created_at), count(id)
FROM posts
WHERE created_at between '01/01/14' and '01/01/19'
GROUP BY DATE_TRUNC('day', created_at), EXTRACT(hour FROM created_at)
ORDER BY DATE_TRUNC('day', created_at) ASC, EXTRACT(hour FROM created_at) ASC
"
}
function topics_by_hours(){
    sql "
SELECT DATE_TRUNC('day', created_at), EXTRACT(hour FROM created_at), topic_id, count(id)
FROM posts
WHERE created_at between '01/01/14' and '01/01/19'
GROUP BY DATE_TRUNC('day', created_at), EXTRACT(hour FROM created_at), topic_id
HAVING count(id)>50
ORDER BY DATE_TRUNC('day', created_at) ASC, EXTRACT(hour FROM created_at) ASC
"
}

function posts_per_topic(){
    sql "
WITH post_counts AS (
    SELECT COUNT(*) as post_count, topic_id
    FROM posts
    GROUP BY topic_id
)
SELECT post_count, COUNT(*)
FROM post_counts
GROUP BY post_count
ORDER BY post_count ASC
"
}

function time_read(){
    sql "
SELECT u.username, (time_read /86400 || ' day(s)') || ' ' || TO_CHAR((time_read % 86400 || ' second')::interval, 'HH24:MI:SS') AS duration
FROM user_stats us
JOIN users u ON u.id=us.user_id
ORDER BY time_read DESC
LIMIT $LIMIT"
}

function time_read_post_timings(){
    sql "
WITH us AS (
    SELECT sum(LEAST(msecs, 100000000000))/1000000 AS time_read, user_id
    FROM post_timings
    GROUP BY user_id
)
SELECT u.username, (time_read /86400 || ' day(s)') || ' ' || TO_CHAR((time_read % 86400 || ' second')::interval, 'HH24:MI:SS') AS duration
FROM us
JOIN users u ON u.id=us.user_id
ORDER BY time_read DESC
LIMIT $LIMIT"
}

function x1000_likes(){
    sql "
WITH liked_posts AS (
    SELECT pa.post_id, p.post_number
    FROM post_actions pa
    JOIN users u on u.id=pa.user_id
    JOIN posts p on p.id=pa.post_id
    WHERE pa.post_action_type_id=2 AND
        u.username LIKE '$NAME'
    ORDER BY p.post_number ASC
),
StartingPoints AS (
    SELECT post_number, ROW_NUMBER() OVER(ORDER BY post_number) AS rownum
    FROM liked_posts as A
    WHERE NOT EXISTS (
        SELECT post_number
        FROM liked_posts as B
        WHERE B.post_number = A.post_number - 1
    )
),
EndingPoints AS (
    SELECT post_number, ROW_NUMBER() OVER(ORDER BY post_number) AS rownum
    FROM liked_posts as A
    WHERE NOT EXISTS (
        SELECT post_number
        FROM liked_posts as B
        WHERE B.post_number = A.post_number + 1
    )
)
SELECT S.post_number AS start_range, E.post_number AS end_range
FROM StartingPoints AS S
JOIN EndingPoints AS E ON E.rownum = S.rownum
;"
}


function primes_1(){
    sql "
WITH digits(i) AS (
    SELECT 1 AS i UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9 UNION ALL
    SELECT 0
),
sequence(i) AS (
    SELECT d1.i + (10*d2.i) + (100*d3.i) + (1000*d4.i) + (10000*d5.i) + (100000*d6.i)
    FROM digits AS d1,
    digits AS d2,
    digits AS d3,
    digits AS d4,
    digits AS d5,
    digits AS d6
)
SELECT      n1.i
FROM        sequence AS n1
WHERE       n1.i > 1
    AND         n1.i < 100
    AND NOT EXISTS(
        SELECT    *
        FROM      sequence AS n2
        WHERE     n2.i > 1
        AND       n2.i < n1.i
        AND       n1.i % n2.i = 0)
    ORDER BY i ASC
"
# SELECT max(i) FROM sequence; "
}

user=`whoami`
if [ "$user" != "postgres" ]; then
    echo "Not running as postgres. Execute one of:"
    echo "   su - root -c \"su - postgres\""
    echo "   sudo su - postgres"
    echo "To continue."
    exit 1;
fi


function help(){ # Show this help
    grep "^function.*#" $0 | sed -e "s/^function \([^(]*\)(){ #\(.*\)$/\1 -- \2/g"
}


if [ "_$1" = "_" ]; then
    help
else
    grep "$1(" $0 | sed 's/^[^#]*//g'
    tic=$(($(date +%s%N)/1000000))
    "$@" | sed -e '
s/c09fa970-5a9a-11e4-8ed6-0800200c9a66/[UUID removed to prevent this thread accidentally being marked as excluded]/g
'
    toc=$(($(date +%s%N)/1000000))
    delta=$(($toc - $tic))
    LC_NUMERIC=C LC_COLLATE=C
    printf 'Elapsed: %gs\n' $(bc <<< "scale=3; ${delta}/1000")
    echo -n "Backup taken: "; backup_date
fi