Pass a parameter into an SQL file

Question

In Oracle we have a tiny sql file (truncate_table.sql) that calls a function that truncates a table:

BEGIN
  DBMS_OUTPUT.PUT_LINE('FuncTruncTable executing...');
     IF ( FuncTruncTable(upper('&1'))=FALSE) THEN
  DBMS_OUTPUT.PUT_LINE('FuncTruncTable failed');
     END IF;
END;
/

For &1, we pass schema_name.table_name. I realize that table names in PostgreSQL are in lower case. So, in PostgreSQL I tried

IF ( select FuncTruncTable(lower('&1'))=FALSE) THEN....;

This SQL file is called by a shell script (truncate_single_table.sh) that basically looks like this

TableName=$1
LOG_FILE=/some_dir/log/truncate_log.txt
sqlplus user_name/user_pass @/some_dir/sql/truncate_table.sql $TableName > $LOG_FILE

truncate_single_table.sh is called by another shell script truncate_tables.sh.

truncate_tables.sh assembles a bunch of tables and calls `truncate_single_table.sh to truncate them one by one as follows:

. /some_dir/bin/truncate_single_table.sh schema_name.TABLE_1 NEW
. /some_dir/bin/truncate_single_table.sh schema_name.TABLE_2 APPEND
. /some_dir/bin/truncate_single_table.sh schema_name.TABLE_3 APPEND

and so forth.

NEW and APPEND are used in order to count the tables - the number of the tables that have to be truncated has to match the number of the actually truncated tables. If an error occurs, the job sends a warning because (TotalNumber - number of errors) <> TotalNumber.

I'm trying to convert the process to PostgreSQL. I'm totally new to PostgreSQL. Simply replacing

sqlplus user_name/user_pass @/some_dir/sql/truncate_table.sql $TableName > $LOG_FILE

with

psql -d "dbname=$dbname user='user_name' password='user_pass' host=$HostName" -f /some_dir/sql/truncate_gnxsawork_table.sql $TableName > $LOG_FILE

doesn't work.

Perhaps different options (other than -d, -f) should be used that I'm not aware of.

Answer 1

You can pass the table name to psql as a variable:

psql -v tabname="$TableName" -f script.sql -d ...

and use it in the script as

DO $$BEGIN RAISE NOTICE 'FuncTruncTable executing...'; END;$$;
SELECT NOT functrunctable(upper(:'tabname')) AS result \gset
\if :result
DO $$BEGIN RAISE NOTICE 'FuncTruncTable failed'; END;$$;
\endif