Thread: PL/pgSQL
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.
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 ?
Thank you,
---------------------------
Sergei Chernev
Internet: ser@nsu.ru
Phone: +7-3832-397354
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