Pass tablename as sql script argument

Question

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?

Answer 1

You may use the psql variable table like this:

ALTER TABLE :table ADD COLUMN...

psql will interpolate :table and send this query:

  ALTER TABLE realtablename ADD COLUMN...

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:

SELECT pgr_createTopology(:'table', 0.00001, 'geom', 'gid');

then :table will be interpolated to a literal string, so the query actually produced would be like:

SELECT pgr_createTopology('realtablename', 0.00001, 'geom', 'gid');

---

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:

#!/bin/bash
# ...
tablename="$1"
psql [options] <<EOF
ALTER TABLE ${tablename} ADD COLUMN "source" integer;
-- ...
CREATE INDEX ${tablename}_source_idx ON $tablename("source");

EOF

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.

/* inside SQL script */
CREATE FUNCTION pg_temp.create_stuff(tablename text) returns void AS $$
BEGIN
  EXECUTE format('ALTER TABLE %I ADD COLUMN source INTEGER', tablename);
  EXECUTE format('ALTER TABLE %I ADD COLUMN target INTEGER', tablename);

  -- 
  EXECUTE format('SELECT pgr_createTopology(%L, 0.00001, ''geom'', ''gid'')',
    tablename);
  --
  EXECUTE format('CREATE INDEX %I ON %I(source)',
   tablename||'_source_idx', tablename);
  EXECUTE format('CREATE INDEX %I ON %I(target)',
   tablename||'_target_idx', tablename);
END
$$ language plpgsql;

/* still inside SQL script, call function with table as string literal */
select pg_temp.create_stuff(:'table');

I don't suggest a DO block because they don't take input parameters.