In addition to what @filiprem wrote, here is how you do this properly:
...
DECLARE
tbl_var text := 'myTable'; -- I would not use mixed case names ..
BEGIN
EXECUTE '
CREATE TABLE ' || quote_ident(tbl_var) || '(
foo integer NOT NULL,
bar text NOT NULL)';
...
Use quote_ident()
to avoid SQL injection or syntax errors. It will quote names with non-standard characters or reserved words.
I also replaced the double-quotes you had around the string value in your example with single-quotes.
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 manual for psql:
The body of a
DO
statement (or function) is a quoted literal. Dollar-quoted in this case, but all the same:To enable SQL interpolation, do it outside the string and then concatenate - which gets tedious quickly ...
One way to make it simpler is to let Postgres do the string processing and then execute the result using
\gexec
. Call from psql:(
%
has a special meaning insideformat()
, using%%
to get a single%
in the string.)Also note the syntax
:'now_utc'
to get a quoted string.To just use the current UTC timestamp, you don't need all this complication:
DO
statements are not meant to take parameters. It's simpler to create a (temporary) function and pass value(s) as function parameter(s) in the call:Call with SQL interpolation in psql:
Finally, for passing values, you can also (ab)use a Postgres "customized option" (as session variable):
Note that the the value is stored as
text
and you may need to cast.Details:
But then you might skip the psql variable and set the option in Postgres directly ...