Postgresql – COPY command only works as command line argument, not as part of script

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.

It's not the client application that makes the results differ, but the database user you're connected with.

In 9.3, schemata returns only the schemas that the session's user owns, either directly or indirectly through a granted role.

The doc says:

The view schemata contains all schemas in the current database that are owned by a currently enabled role.

Note that it was changed in later versions to be less restrictive.

See What permissions are required to return rows from information_schema.schemata? (9.3)
and How to check if PostgreSQL public schema exists? on stackoverflow