A few PostgreSQL tricks

Running a single SQL command on a remote host:

A script to create the database:

#!/bin/bash

CONFFILE=project.conf

die() <snipped for brevity>

[ -f ${CONFFILE} ] || die 1 "Config file ${CONFFILE} not found."

. ${CONFFILE}

# These can be used to convey username/passwd to pgsql without exposing them on the command line
export PGUSER=postgres
unset PGPASSWORD

# Variables used below were (of course) set in ${CONFFILE}
cat <<EOF |su - postgres -c "psql"
CREATE USER ${DBOWNER_UNAME} WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT;
CREATE USER ${DBWEBADMIN_UNAME} WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD '${DBWEBADMIN_PASSWD}';
CREATE USER ${DBWEBREADER_UNAME} WITH NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT PASSWORD '${DBWEBREADER_PASSWD}';
CREATE DATABASE ${DBNAME} WITH OWNER ${DBOWNER_UNAME};
EOF
	  

Makefile-snippet to create database creation script from Dia file:

tables.sql: db.dia
parsediasql --file $< --db postgres > $@
sed -i 's%default UNIQUE NOT NULL%UNIQUE NOT NULL%' $@
sed -i 's%default unique not null%unique not null%' $@
sed -i 's%^\[WARN\]%-- [WARN]%g' $@
sed -i 's%^\[INFO\]%-- [INFO]%g' $@
	  

Script snippet to run generated *.sql inside psql under some account:

. ${CONFFILE}

echo "Running $0"

# Fill the database
( echo "\c ${DBNAME} - ${DBOWNER_USERNAME}"
cat sp.sql )|su - postgres -c "psql"
	  

(${CONFFILE} of course contains the login data for the database:

<snip>
export PGUSER=${DBWEBADMIN_UNAME}
export PGPASSWORD=${DBWEBADMIN_PASSWD}
<snip>
	  

Creating a named stored procedure in pl/pgsql:

-- This needed in order to use PlPgsql stored procedures at all
CREATE TRUSTED LANGUAGE plpgsql
HANDLER "plpgsql_call_handler";

GRANT USAGE ON LANGUAGE plpgsql TO public;

-- merge-account
-- ToDo: -
CREATE OR REPLACE FUNCTION merge_account(in_row account) RETURNS INTEGER AS
$MERGE_ACCOUNT$
DECLARE
account_id INTEGER := NULL;
BEGIN
SELECT id FROM account
WHERE    uid = in_row.uid
AND username = in_row.username
AND      gid = in_row.gid
INTO account_id;
IF NOT FOUND THEN
  in_row.id := nextval('account_id_seq');
  INSERT INTO account VALUES (in_row.*)
  RETURNING id INTO account_id;
END IF;
RETURN account_id;
END;
$MERGE_ACCOUNT$
LANGUAGE 'plpgsql';
	  

This is also a stored procedure that could be used with a merge rule:

CREATE OR REPLACE RULE r_account_insert AS
ON INSERT TO account DO INSTEAD SELECT import_account(NEW);
	  

Catching exceptions:

BEGIN
  <snip>
EXCEPTION 
WHEN invalid_text_representation THEN
  raise notice 'In function import_event, cannot insert machine, (most likely because of invalid MAC or IP): %', in_row;
  RETURN false;
WHEN not_null_violation THEN
  RETURN false;
EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Couldnt insert machine %', i_machine;
  -- INSERT INTO imperfect_event (id) VALUES (i_machine.id);
END;
	  

Subselect-and-join soup (this query is to figure out who got root rights since the last time the machine was installed) :

select 
  m.visiblefqdn as visiblefqdn,
  m.mac as mac,
  last_installtime.osversion as osversion,
  a.username as username,
  a.uid as uid,
  last_installtime.start as start,
  last_installtime.max_seen as last_seen
from (
 select latest_installtime.machineid as machineid,
        rr_installtime.accountid as accountid,
        rr_installtime.os as os,
        rr_installtime.osversion as osversion,
        latest_installtime.max_seen as max_seen,
        latest_installtime.start as start  
 from (
       select m.id as machineid,
              rra.accountid as accountid,
              i.id,
              i.os as os,
              i.osversion as osversion,
              i.start,
              i.last_seen
       from installtime i
       join rootrights_assn rra
       on (rra.uptimeid = i.id)
       join machine m
       on (i.machineid = m.id)
      ) rr_installtime
 join (
       select * 
       from (
             select max(last_seen) max_seen 
             from installtime
             group by machineid
            ) as max_installtime
             join installtime 
             on (max_seen = last_seen)
      ) latest_installtime
 on (        rr_installtime.os = latest_installtime.os
  and rr_installtime.osversion = latest_installtime.osversion
  and rr_installtime.machineid = latest_installtime.machineid
    )
) last_installtime
join machine m
on (last_installtime.machineid = m.id)
join account a
on (last_installtime.accountid = a.id)
order by last_seen desc, start desc, visiblefqdn, username;
	  

select id, max(last_seen), os
from installtime
group by id;
		

Concatenating multiple values from a column into one line when doing group-by:

  SELECT uid, array_to_string(array_agg(username),', ') FROM taccount GROUP BY uid;
	  

(This gets you '1000 | erwin, bart, george' if all these users had uid 1000.)