PL/pgSQL

Hello Sergei,

mercoledО©╫, 11 novembre 98, you wrote:

SC> Hello,
SC> I've installed postgresql-v6.4 on BSDI-3.1
SC> But, there are two problems with PL/pgSQL:
SC> First: I have to install plpgsql language on every database,
SC> I wonder, if I can install it for everyone.
If you install PL/pgSQL on template1 it will automatically be copied to every
database you will create. This is not valid for databases created
before you install it on template1.

SC> Second: When I install plpgsql language, and prepare one test:
=>>CREATE FUNCTION f_test () RETURNS abstime AS '
=>> BEGIN
=>> RETURN \'now\';
=>> END;
=>>' LANGUAGE 'plpgsql';
SC> CREATE
SC> EOF
SC> Then I do:
=>> select f_test();
SC> It tells me, that:
SC> pqReadData() -- backend closed the channel unexpectedly.
SC> This probably means the backend terminated abnormally before or
SC> while pr
SC> ocessing the request.
SC> We have lost the connection to the backend, so further processing is
SC> impossible.
SC> Terminating.
SC> and exit me to the shell. What is wrong ?

On my Linux box it works:

CREATE FUNCTION f_test () RETURNS abstime AS '
BEGIN
RETURN \'now\';
END;
' LANGUAGE 'plpgsql';
CREATE

select f_test();
f_test
----------------------
1998-11-11 18:57:46+01
(1 row)

-Jose'-

[Charset koi8-r unsupported, filtering to ASCII...]

Hello,
I've installed postgresql-v6.4 on BSDI-3.1
But, there are two problems with PL/pgSQL:
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.

Second: When I install plpgsql language, and prepare one test:
=>CREATE FUNCTION f_test () RETURNS abstime AS '
=> BEGIN
=> RETURN \'now\';
=> END;
=>' LANGUAGE 'plpgsql';
CREATE
EOF
Then I do:
=> select f_test();
It tells me, that:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or
while pr
ocessing the request.
We have lost the connection to the backend, so further processing is
impossible.
Terminating.
and exit me to the shell. What is wrong ?

It shouldn't be doing this, but it is. Something wrong about the way
bsdi 3.* is doing dynamic linking, I think. bsdi 4.0, which I have here
works.

Can you do it in gdb and send the backtrace of the crash?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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

Added to src/pl/plpgsql in both trees. It is not installed
automatically. Can someone figure out how this should be handled?

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

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026