Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:
#!/bin/sh
psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!
echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
kill $psql_pid
rm -f $psql_pipe
' EXIT
echo "begin;" >&3
echo "select now();" >&3
sleep 2
echo "select now();" >&3
sleep 2
echo "end; \quit" >&3
wait $psql_pid
Note that you can't simply do echo "sql" >$psql_pipe
since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.
Strictly speaking, there is no such thing as a "plpgsql script" - PL/pgSQL is the default procedural language of PostgreSQL. It's either an SQL script or a plpgsql function / procedure. Your example seems to indicate an SQL script.
You could create a (server-side ) plpgsql (or sql) function instead, that takes any number of arguments. It's very simple as long as the arguments are values
. It gets a bit more complicated if the arguments include identifiers. Then you'll have to use PL/pgSQL with dynamic SQL and EXECUTE
.
PL/pgSQL is pre-installed by default in PostgreSQL 9.0 or later. You have to install it once per database in Postgres 8.3, though:
CREATE LANGUGAGE plpgsql;
Speaking of the version: you should consider upgrading to a current version of PostgreSQL. v8.3 is very old by now, end-of-life in early 2013.
Since you seem to have a ready SQL script I'll demonstrate an SQL function. Simple dummy function with two integer arguments:
CREATE OR REPLACE FUNCTION func(int, int)
LANGUAGE sql RETURNS void AS
$func$
UPDATE tbl1 SET col1 = $1 WHERE id = $2;
UPDATE tbl2 SET col1 = $1 WHERE id = $2;
$func$;
You can find many more sophisticated examples for plpgsql here on dba.SE or on SO.
You can call this function and hand in parameters in a shell script:
Basic example for a call in a shell script that uses input parameters for integer parameters (no single-quotes around the value needed):
psql mydb -c "SELECT func($1, $2)"
Or with any data type:
psql mydb -c "SELECT func2('$1'::text, '$2'::numeric)"
-c
executes one command string and then exits.
More about command line arguments of psql in the manual.
Best Answer
The
-t
(--tuples-only
) option might be used also: