Re: [GENERAL] PL/pgSQL

Bruce Momjian wrote:
  >> First: I have to install plpgsql language on every database,
  >> I wonder, if I can install it for everyone.
  >
  >If you put it in template1, then every new database gets it.

As a contribution, here is a script to do this for existing databases
(including template1):

=================== begin script enable_pgpl ==============================
#!/bin/sh

# Enable the PL procedural language for PostgreSQL in one or more
# existing databases.
#
# This script should be run by the PostgreSQL superuser

enable_database() {
    if ! psql -d $1 -qtc "select count(*) from pg_language where lanname='plpgsql'" >$TMPFIL2 2>&1
    then
        echo "Cannot connect to $1"
        exit 2
    fi
    if [ `cat $TMPFIL2` -eq 0 ]
    then
        if ! psql -d $1 <$sqlfile
        then
            echo "Failed to add PL to $1"
            exit 2
        fi
        echo "PL added to $1"
    else
        echo "PL is already enabled in $1"
    fi

}

# Execution starts here

TMPFILE=`mktemp /tmp/enable_pgpl.XXXXXX`
TMPFIL2=`mktemp /tmp/enable_pgpl.XXXXXX`
trap "rm $TMPFILE $TMPFIL2" EXIT

sqlfile=${PGLIB:=/usr/local/pgsql/lib}/mklang_pl.sql
if [ ! -f $sqlfile ]
then
    echo "Cannot find mklang_pl.sql"
    exit 2
fi

if [ -z "$1" ]
then
    echo "Syntax: $0 --all | database ..."
    exit 1
fi

if [ $1 = "--all" ]
then
    if ! psql -t -c "select datname from pg_database order by datname" >$TMPFILE
    then
        echo Cannot select databases
        exit 2
    fi
    for db in `cat $TMPFILE`
    do
        enable_database $db
    done
else
    while [ -n "$1" ]
    do
        db=$1
        enable_database $db
        shift
    done
fi
========================= end ================================

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Be of good courage, and he shall strengthen your
      heart, all ye that hope in the LORD."
                                  Psalms 31:24