I'm trying to automatize a bunch of postgreSQL process, namely to use PostGIS and pgRouting extensions, so for that purpose I created a sql script file that I could run on command line passing in an input argument, a table name, like the following:
ALTER TABLE ''''||:table::char(52)||'''' ADD COLUMN "source" integer;
ALTER TABLE ''''||:table::char(52)||'''' ADD COLUMN "target" integer;
SELECT pgr_createTopology(''''||:table::char(52)||'''', 0.00001, 'geom', 'gid');
CREATE INDEX ''''||:table::char(52)||''''_source_idx ON ''''||:table::char(52)||''''("source");
CREATE INDEX ''''||:table::char(52)||''''_target_idx ON ''''||:table::char(52)||''''("target");
So when I try to run in the command line, as such:
psql -h my_host -U my_user -d my_database -c table="my_table" -f /script/pgRouting.sql
I get a syntax error, so what's the correct way of doing this?
Best Answer
You may use the psql variable
table
like this:psql will interpolate
:table
and send this query:to the SQL interpreter, when it's called with
-v table=realtablename
There's a pre-condition, though, that the table's name doesn't require quoting.
The SELECT may be written as:
then
:table
will be interpolated to a literal string, so the query actually produced would be like:However, this technique can't really be used for more complicated things like the
CREATE INDEX
mentioned, as it doesn't need the variable's value but another value based on it.Personally for that sort of thing, I would embed the SQL script inside a shell script and let the shell do the interpolating. Example:
The nice thing with the shell is that it has no problem concatenating with
${tablename}_source_idx
,whereas psql will not accept:"tablename"_source_idx
or (as far as I know) offer a simple alternative.Anyway, if
$tablename
can contain problematic characters such as separators or quotes, either double or single or both, none of the above is good enough. As your SQL statements need the table's name both as a literal string and as an identifier, the problem is not as easy as it would seem.The ultimate solution, to cover for any syntactic hazard in the table's name, could be to define and call a temporary plpgsql function that uses dynamic SQL functionalities.
I don't suggest a
DO
block because they don't take input parameters.