July 2011
Running a single SQL command on a remote host:
user@host:~$ psql --host host.remote.com --port 5433 databasename -c "select max (id) from mytable"
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;
The innermost query: for each machineid in table installtime, select the latest | |
The temporary-during-this-query table select id, max(last_seen), os from installtime group by id;
Either we must group by |
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.)