Warning: this is an htmlized version!
The original is across this link,
and the conversion rules are here.
#######
#
# E-scripts on PostGres.
#
# Note 1: use the eev command (defined in eev.el) and the
# ee alias (in my .zshrc) to execute parts of this file.
# Executing this file as a whole makes no sense.
#
# Note 2: be VERY careful and make sure you understand what
# you're doing.
#
# Note 3: If you use a shell other than zsh things like |&
# and the for loops may not work.
#
# Note 4: I always run as root.
#
# Note 5: some parts are too old and don't work anymore. Some
# never worked.
#
# Note 6: the definitions for the find-xxxfile commands are on my
# .emacs.
#
# Note 7: if you see a strange command check my .zshrc -- it may
# be defined there as a function or an alias.
#
# Note 8: the sections without dates are always older than the
# sections with dates.
#
# This file is at <http://angg.twu.net/e/postgres.e>
#           or at <http://angg.twu.net/e/postgres.e.html>.
#        See also <http://angg.twu.net/emacs.html>,
#                 <http://angg.twu.net/.emacs[.html]>,
#                 <http://angg.twu.net/.zshrc[.html]>,
#                 <http://angg.twu.net/escripts.html>,
#             and <http://angg.twu.net/>.
#
#######




# «.tutorial»			(to "tutorial")
# «.commands_on_template1»	(to "commands_on_template1")
# «.commands_on_edrxdb»		(to "commands_on_edrxdb")
# «.eepitch-edrxdb»		(to "eepitch-edrxdb")
# «.libpq»			(to "libpq")
# «.libpq-upstream»		(to "libpq-upstream")
# «.postgres-upstream»		(to "postgres-upstream")
# «.testlibpq»			(to "testlibpq")
# «.gdb-ing-psql»		(to "gdb-ing-psql")
# «.psql-prefers-socket:log»	(to "psql-prefers-socket:log")
# «.allow-tcp-access»		(to "allow-tcp-access")
# «.plpgsql»			(to "plpgsql")
# «.luapsql»			(to "luapsql")
# «.squeeze»			(to "squeeze")



# (find-es "sql")


#####
#
# postgresql on sarge
# 2004aug25
#
#####


# (find-progoutput "grep-available 'Source: postgresql'")
# (find-progoutput "apt-cache showsrc postgresql")

apti postgresql postgresql-client postgresql-doc

# (find-status   "postgresql")
# (find-vldifile "postgresql.list")
# (find-udfile   "postgresql/")
# (find-status   "postgresql-client")
# (find-vldifile "postgresql-client.list")
# (find-udfile   "postgresql-client/")
# (find-status   "postgresql-doc")
# (find-vldifile "postgresql-doc.list")
# (find-udfile   "postgresql-doc/")

# (code-c-d "pgdoc"  "/usr/share/doc/postgresql-doc/")
# (code-c-d "pghtml" "/usr/share/doc/postgresql-doc/html/")
# (code-c-d "pgtut"  "/usr/share/doc/postgresql-doc/tutorial/")

# (find-pgdocfile "")
# (find-pghtmlfile "")
# (find-pghtmlw3m "index.html")
# (find-pgtutfile "")
# (find-pgtutfile "README" "the -s (single-step) flag")
# (find-pgtutfile "basics.source")

# «tutorial»  (to ".tutorial")
# (find-enode "Specifying File Variables")
# (find-efile "progmodes/sql.el")
# (find-efile "progmodes/sql.el" "sql-product")
#*
# Untabify:
rm -Rv ~/usrc/pgtut/
mkdir  ~/usrc/pgtut/
cd /usr/share/doc/postgresql-doc/tutorial/
for i in *; do
  echo '-- -*- mode: sql -*-' > ~/usrc/pgtut/$i
  col -x < $i >> ~/usrc/pgtut/$i
done

#*
# (find-fline "~/usrc/pgtut/")
# (find-fline "~/usrc/pgtut/basics.source")

* (eechannel-xterm "postgres")
* (eepitch-shell)

                  dropdb     -e edrxdb
sudo -u postgres  dropuser   -e                         edrx
sudo -u postgres  createuser -e --createdb --no-adduser edrx
                  createdb   -e edrxdb
 
# psql -d edrxdb
PAGER=cat \
  psql -d edrxdb
# (find-fline "~/usrc/pgtut/basics.source")





#####
#
# creating an dropping users and databases by commands on template1
# 2006jul10
#
#####

# «commands_on_template1»  (to ".commands_on_template1")

* (eepitch-shell)
sudo -u postgres psql template1
  DROP   DATABASE edrxdb;
  DROP   USER     edrx;
  CREATE USER     edrx    CREATEDB NOCREATEUSER;
  CREATE DATABASE edrxdb;
  \q

# When PAGER is "less" or "more" we get this message in shell buffers:
#   WARNING: terminal is not fully functional
# For some reason "PAGER=cat sudo -u postgres psql template1" doesn't
# work, we need either the zsh 2-line solution or "\pset pager off".

# (find-man "1 psql")

* (eepitch-shell)
sudo -u postgres zsh
PAGER=cat psql template1
  SELECT * FROM PG_DATABASE;
  \q
exit; # exit "sudo -u postgres zsh"

* (eepitch-shell)
sudo -u postgres psql template1
  \pset pager off
  SELECT * FROM PG_DATABASE;
  \q

# «commands_on_edrxdb»  (to ".commands_on_edrxdb")
# (find-fline "~/usrc/pgtut/basics.source")

* (eepitch-shell)
psql edrxdb
  CREATE TABLE weather (
    city varchar(80), temp_lo int, temp_hi int, prcp real, date date );
  CREATE TABLE cities ( name varchar(80), location point );
  INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
  INSERT INTO cities  VALUES ('San Francisco', '(-194.0, 53.0)');
  INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
               VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
  INSERT INTO weather (date, city, temp_hi, temp_lo)
               VALUES ('1994-11-29', 'Hayward', 54, 37);
  \pset pager off
  SELECT * FROM weather;
  SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
  \q




#####
#
# eepitch-edrxdb
# 2006jul25
#
#####

# «eepitch-edrxdb»  (to ".eepitch-edrxdb")
# (find-efile "progmodes/sql.el")
# Note: this is not the right way to run psql in a comint buffer - it
# doesn't understand prompts correctly, etc... sql.el probably knows
# the right way.

(defun eepitch-edrxdb () (interactive)
  (eepitch (ee-comint "psql edrxdb" "psql" "edrxdb")))

* (eepitch-edrxdb)
CREATE TABLE weather (
  city varchar(80), temp_lo int, temp_hi int, prcp real, date date );
\q





#####
#
# some fossils
# 2006jul10
#
#####

# (find-fline "/etc/postgresql/postgresql.env")
# (find-man "1 createdb")
# (find-man "1 createuser")
# (find-man "1 dropdb")
# (find-man "1 dropuser")

# (find-man "1 pg_dump")
# (find-man "1 pg_dumpall")
# (find-man "1 pg_restore")
# (find-man "1 pg_wrapper")
# (find-man "1 psql")
# (find-man "1 vacuumdb")

# (find-fline "/etc/postgresql/postmaster.conf")
# (find-fline "/var/lib/postgres/data/")
# (find-fline "/usr/lib/postgresql/share/pgaccess")

* (eechannel-xterm "postgres")
* (eechannel "postgres")

sudo -u postgres zsh
psql



DROP USER edrx;
DROP USER
sudo -u postgres   createuser -e --createdb --no-adduser edrx
CREATE USER edrx CREATEDB NOCREATEUSER;



sudo -u postgres   dropuser -e edrx
sudo -u postgres   createuser -e --createdb --no-adduser edrx
psql -d edrx




#####
#
# basic administrative commands: creating a user
# 2005jan09
#
#####

* (eechannel-xterm "postgres")
* (eechannel "postgres")

# At the start only the user "postgres" can do things.
# The next commands are to be run by "postgres" user.
#
sudo -u postgres zsh

# Dropping and recreating the user "edrx" - the easy way
# (find-man "1 createuser")
# (find-man "1 dropuser")
#
dropuser -e edrx
createuser -e --createdb --no-adduser edrx

# Basic psql usage: "\" commands
# (find-man "1 psql")
#
PAGER=cat \
psql template1
\l
\?
\h
-- \h *
\h SELECT
\q
  
# Dropping and recreating the user "edrx" - the SQL way
#
psql template1
DROP USER edrx;
CREATE USER edrx CREATEDB NOCREATEUSER;
\q

exit

# Now that the user edrx exists...
# (find-sh "psql -c '\\h *' template1")
# (find-sh "psql -c '\\h *' template1" "CREATE USER")


# (find-man "1 pg_dump")
# (find-sh "pg_dump template1")




# (find-man "1 dropdb")


# (find-man "1 clusterdb")
# (find-man "1 createdb")
# (find-man "1 createuser")
# (find-man "1 dropdb")
# (find-man "1 dropuser")
# (find-man "1 pg_dump")
# (find-man "1 pg_dumpall")
# (find-man "1 pg_restore")
# (find-man "1 pg_wrapper")
# (find-man "1 psql")
# (find-man "1 vacuumdb")



psql

/home/edrx/e(edrx:pe)# sudo -u postgres zsh
/home/edrx/e(postgres:pe)# psql
psql (pg_wrapper): No database specified



sudo -u postgres psql -c '\\l' template1
sudo -u postgres psql -c '\\?' template1

# (find-sh "sudo -u postgres psql -c '\\l' template1")
# (find-sh "sudo -u postgres psql -c '\\?' template1")


DROP USER edrx;
DROP USER
sudo -u postgres   createuser -e --createdb --no-adduser edrx
CREATE USER edrx CREATEDB NOCREATEUSER;

DROP USER edrx;
CREATE USER edrx CREATEDB NOCREATEUSER;




#####
#
# running the tutorial
# 2005feb16
#
#####

# 



#####
#
# libpq: calling postgres from C
# 2006jun22
#
#####

# «libpq»  (to ".libpq")
# (find-udfile "postgresql-doc/html/")
# (find-udfile "postgresql-doc/html/" "libpq")
# (find-udw3m  "postgresql-doc/html/libpq.html")
# (find-udw3m  "postgresql-doc/html/libpq-example.html")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/Makefile")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/testlibpq.c")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/testlibpq2.c")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/testlibpq2.sql")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/testlibpq3.c")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/testlibpq3.sql")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/testlibpq4.c")
# (find-udfile "postgresql-doc/libpq3/libpq/examples/testlo.c")

# (find-udfile "postgresql-doc/libpq3/libpq/examples/Makefile")
# (find-udfile "postgresql-doc/")

#*
# Functions used in the examples:

cd /usr/share/doc/postgresql-doc/libpq3/libpq/examples/
cat *.c | lua50 -e '
    string.gsub(io.read("*a"), "([A-Za-z0-9_]+)", print)
  ' |& sort | uniq | tee /tmp/o

grep '^P[GQ]' /tmp/o | tee /tmp/o2

# (find-fline "/tmp/o")
# (find-fline "/tmp/o2")

#*



#####
#
# libpq-examples: compiling and running from upstream
# 2006jun26
#
#####

# «libpq-upstream»  (to ".libpq-upstream")
# «postgres-upstream»  (to ".postgres-upstream")
# http://www.postgresql.org/ftp/source/v7.4.13/
# (code-c-d "pg"   "~/usrc/postgresql-7.4.13/")
# (code-c-d "pgex" "~/usrc/postgresql-7.4.13/src/test/examples/")
#*
rm -Rv ~/usrc/postgresql-7.4.13/
tar   -C ~/usrc/ -xvzf ~/tmp/postgresql-7.4.13.tar.gz
cd     ~/usrc/postgresql-7.4.13/
find * -type f                | sort > .files
find * -type f -name '*.[ch]' | sort > .files.ch
etags $(cat .files.ch)
./configure |& tee oc
make        |& tee om

#*
cd     ~/usrc/postgresql-7.4.13/
cd     ~/usrc/postgresql-7.4.13/src/test/examples/
make |& tee om

#*
cd     ~/usrc/postgresql-7.4.13/doc/
mkdir html/
tar -C html/ -xvzf postgres.tar.gz

#*
# (find-pgfile "doc/html/")

# (find-anggfile "usrc/postgresql-7.4.13/doc/")

# (find-pgtag "PQexec")
# (find-pgfile "")
# (find-pgfile ".files")
# (find-pgfile "src/backend/libpq/")
# (find-pgexfile "")
# (find-pgexfile "testlibpq.c")
# (find-pgexfile "testlibpq2.c" "Start this program, then from psql")
# (find-pgexfile "testlibpq2.sql")
# (find-pgexfile "testlibpq3.c")
# (find-pgfile "src/test/examples/")
# (find-pgfile "src/test/examples/om")
# (find-fline "~/tmp/")
# (find-pgfile "GNUmakefile")
# (find-pgfile "om")
# (find-pgfile "")

# «testlibpq»  (to ".testlibpq")
#*
* (eepitch-shell)
cd     ~/usrc/postgresql-7.4.13/src/test/examples/
./testlibpq dbname=template1
./testlibpq dbname=edrxdb
./testlibpq
./testlibpq2

#*




#####
#
# running psql through gdb
# 2006jul10
#
#####

# «gdb-ing-psql»  (to ".gdb-ing-psql")
# (find-es "ruby" "mident-error-gdb-psql")
# (find-pgfile "om" "-o psql")
# (find-psqlfile "")
# (find-psqlfile "Makefile")
# (find-psqlfile "om")
# (find-psqlfile "om-g")
# (find-psqlfile "../../../src/Makefile.global")
# Ppostgres compiles by default without -g, recompile psql
# Here's a more standard way (I haven't tried it yet):
# <AndrewSN> edrx: ./configure --enable-debug --enable-cassert if you
#            plan on debugging the backend
# <edrx> hmm, thanks... I was just trying to recompile only psql using
#        CFLAGS=-g...
# <AndrewSN> edrx: oh, probably don't need --enable-cassert then; but
#            you'll want libpq compiled with -g in that case too, not
#            just psql
# <AndrewSN> edrx: --enable-debug is the way to get -g set though
# <edrx> hmm, true
#*
cd ~/usrc/postgresql-7.4.13/
cd ~/usrc/postgresql-7.4.13/src/bin/psql/
make clean     |& tee omc
make           |& tee om
make clean
make CFLAGS=-g |& tee om-g

#*
# (ee-once (eeb-psqlgdb-start "psql"))
set args edrxdb
br main
run

#*
# I kind of aborted doing this, see the next block...




#####
#
# psql uses unix sockets by default, at /var/run/postgresql/ on Debian
# 2006jul10
#
#####

# «psql-prefers-socket:log»  (to ".psql-prefers-socket:log")
# <AndrewSN> edrx: note that "localhost" is not the same thing as not
#            specifying a hostname at all
# <edrx> hmm, can you say more about that?
# <AndrewSN> edrx: psql by default (and all libpq clients if no
#            hostname, not even localhost, is specified) will use the
#            unix-domain socket
# <AndrewSN> edrx: access via the local socket is controlled by "local"
#            lines in pg_hba.conf
# <edrx> how do I check if I have the unix-domain socket?
# <edrx> is it a file - like a fifo?
# <AndrewSN> edrx: however, if you explicitly specify "localhost" as the
#            host to connect to, then psql or libpq will connect via tcp
#            to 127.0.0.1
# <AndrewSN> edrx: it's an object in the filesystem, yes
# <edrx> I don't have it at /tmp
# <AndrewSN> edrx: some packaging systems put it in /var/run, but either
#            way note that the name starts with a .
# <edrx> hmm, lemme try to ask psql to connect to localhost...
# <edrx> my "ls" shows all files by default
# <AndrewSN> edrx: the name is usually .s.PGSQL.5432
# <edrx> gaaah, silly me, it's there, at /var/run/postgresql/
# <AndrewSN> edrx: though the number changes if you use a non-default
#            port number
# <edrx> (I'm using debian)
# <AndrewSN> yeah, debian is one of those where the packager has imposed
#            his own philosophy where it conflicts with pg's own
# <edrx> and I was so sure that psql was using tcp, just because the
#        socket wasn't at /tmp...
# <edrx> I was just going to try to strace psql, but there's no need
#        anymore
# <AndrewSN> using /var/run makes it harder to run multiple clusters
#            under different users
# <edrx> thanks a lot 8-\
# (find-fline "/var/run/postgresql/")


  conn = PostgresPR::Connection.new('edrxdb', 'edrx', nil, 'unix:/var/run/postgresql/.s.PGSQL.5432')




#####
#
# allowing tcp access
# 2006jul25
#
#####

# «allow-tcp-access»  (to ".allow-tcp-access")
# (find-es "ruby" "postgres-pr-test")
# (find-debpkg-links "postgresql")
# (find-vldifile "postgresql.conffiles")

# (find-fline "$ASROOT/etc/postgresql/pg_ident.conf")
# (find-fline "$ASROOT/etc/postgresql/pg_hba.conf")
# (find-fline "$ASROOT/etc/postgresql/pg_hba.conf" "# Trust localhost and Bernardo")

# All IPv4 connections from localhost
#host    all         all         127.0.0.1         255.255.255.255   ident sameuser
# Trust localhost and Bernardo
host    all         all         127.0.0.1         255.255.255.255   trust
host    all         all         10.126.50.129     255.255.255.255   trust

sudo /etc/init.d/postgresql restart





#####
#
# allowing plpgsql
# 2006aug??
#
#####

# «plpgsql»  (to ".plpgsql")
# (find-pgdocfile "")
# (find-pgdocw3m "plpgsql.html")
# (find-zsh "acse sql | sort")
# (find-zsh "dmissing pgsql")
# (find-es "postgres" "postgres-upstream")
# (find-pgfile ".files" "src/pl/plpgsql/")
# (find-pgfile "src/pl/plpgsql/src/INSTALL")
# (find-pgfile "src/pl/plpgsql/src/INSTALL" "createlang plpgsql DBNAME")
# (find-man "1 createlang")
# (find-man "1 droplang")

                 createlang -l edrxdb
sudo -u postgres createlang plpgsql edrxdb
                 createlang -l edrxdb





#####
#
# luapsql
# 2011jan16
#
#####

# «luapsql»  (to ".luapsql")
# http://code.google.com/p/luapsql/
# http://code.google.com/p/luapsql/downloads/list
# http://luapsql.googlecode.com/files/luapsql-0.1-1.rockspec

# (find-luarocks-links "luapsql" "{ver}" "{s}")

# Install (from the internet):
* (eepitch-shell)
* (eepitch-kill)
* (eepitch-shell)
cd ~/usrc/luarocks/bin/
luarocks install $S/http/luapsql.googlecode.com/files/luapsql-0.1-1.rockspec \
  |& tee oli-luapsql

# Error: Could not find expected file libpq-fe.h for LIBPQ -- you may
# have to install LIBPQ in your system and/or set the LIBPQ_DIR
# variable

# (find-zsh "availabledebs | sort | egrep 'postgres|pg' | grep dev")
# (find-zsh "dmissing libpq-fe.h")
# (find-status   "libpq-dev")
# (find-vldifile "libpq-dev.list")
# (find-udfile   "libpq-dev/")

# (find-zsh "grep-available -i postgres")




#####
#
# PostgreSQL on Squeeze
# 2011nov25
#
#####

# «squeeze»  (to ".squeeze")

apti postgresql postgresql-client postgresql-doc

# (find-status   "postgresql")
# (find-vldifile "postgresql.list")
# (find-udfile   "postgresql/")
# (find-status   "postgresql-8.4")
# (find-vldifile "postgresql-8.4.list")
# (find-udfile   "postgresql-8.4/")
# (find-status   "postgresql-client")
# (find-vldifile "postgresql-client.list")
# (find-udfile   "postgresql-client/")
# (find-status   "postgresql-client-8.4")
# (find-vldifile "postgresql-client-8.4.list")
# (find-udfile   "postgresql-client-8.4/")
# (find-status   "postgresql-client-common")
# (find-vldifile "postgresql-client-common.list")
# (find-udfile   "postgresql-client-common/")
# (find-status   "postgresql-common")
# (find-vldifile "postgresql-common.list")
# (find-udfile   "postgresql-common/")
# (find-status   "postgresql-doc")
# (find-vldifile "postgresql-doc.list")
# (find-udfile   "postgresql-doc/")
# (find-status   "postgresql-doc-8.4")
# (find-vldifile "postgresql-doc-8.4.list")
# (find-udfile   "postgresql-doc-8.4/")













#  Local Variables:
#  coding:               raw-text-unix
#  ee-delimiter-hash:    "\n#*\n"
#  ee-delimiter-percent: "\n%*\n"
#  ee-anchor-format:     "«%s»"
#  modes:                (fundamental-mode emacs-lisp-mode)
#  End: