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; /

&1 is passed as schema_name.table_name. I realize that table names in PG are in lower case. So, in PG I tried IF ( select FuncTruncTable(lower('&1'))=FALSE) THEN....;

This sql file is called by a shell (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 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 ORA- occurs the job send a warning because TotalNumber - number of "ORA-"s <> TotalNumber.

I'm trying to convert the process to Postgres. I'm totally new to PG. Simply replacing sqlplus user_name/user_pass @/some_dir/sql/truncate_table.sql $TableName > $LOG_FILE to 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 (other than -d, -f) should be used that I'm not aware of. I'll greatly appreciate any help. ....but, please don't assume that I already know something )))

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.