Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

4 Answers
4

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 - updates will be discontinued (end-of-life) early in 2013.

Since you seem to have a ready SQL script I'll demonstrate an SQL function. Simple dummy function with two integer arguments:

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.

Thanks for the response - I'm actually quite aware of plpgsql - This script I'm referring to is a file that contains numerous functions. I have a "main" function in the sense of C-oriented programming. The last 2 lines within the script/file are 1) calling the "main" function and then 2) dropping the function. So, in this setup, I essentially have a self-contained script that can be run to do work (psql -f). I like your point about calling a function with the "application args" via psql -c. I'll probably go that route, because I can't go the route of adding values to the postgres.conf file.
–
Jmoney38Jun 18 '12 at 11:52