I have a bash program that is executing a postgreSQL statement:

  psql -q -d $DB --echo-all --set ON_ERROR_STOP=on -f $APP_BASE_PATH/$PROJ/$SCRIPT_NAME -v job_end_bndry_ts="${job_end_bndry_ts}" -v job_exec_uid=${JOB_UID}  > ${LOGPATH}/${MOD_NM}_${LOGTIME}.log 2> >(tee -a $errmsg >&1)

note the -v job_exec_uid=${JOB_UID}

This command will run a file that stages the data from a temporary external table:

CREATE READABLE EXTERNAL TEMPORARY TABLE json_ext (json_data text)
LOCATION ('<location>') FORMAT 'TEXT' (DELIMITER 'OFF' null E'' escape E'\t')
LOG ERRORS SEGMENT REJECT LIMIT 100 PERCENT;

Here is the file that the bash script runs to populate the staging table:

INSERT INTO schema.staging_table
(
JSON_DATAREC_FL,
JOBEXEC_UID,
JSON_FL_ID,
INSRT_TS
)
SELECT
 json_data AS JSON_DATA,
 'job_exec_uid'::integer AS JOBEXEC_UID,
(JSON_DATA::json#>>'{refNumber}') AS JSON_FILE_ID,
now() INSRT_TS
FROM json_ext
;

When I run the bash script, I get the following error: ERROR: invalid input syntax for integer: "job_exec_uid"

Here is the DDL for the staging table:

     Column      |            Type             | Modifiers
----------------- ----------------------------- -----------
 json_fl_id      | character varying(100)      | not null
 jobexec_uid     | integer                     |
 json_datarec_fl | text                        |
 insrt_ts        | timestamp without time zone | not null

CodePudding user response：

The psql variable is evaluated by syntax :variable or :'varable for literal values.

[pavel@localhost postgresql.master]$ psql -v xxx=10 -v yyy='Ahoj'
Assertions: on
psql (16devel)
Type "help" for help.

(2022-10-10 19:42:56) postgres=# select :xxx, :'yyy';
┌──────────┬──────────┐
│ ?column? │ ?column? │
╞══════════╪══════════╡
│       10 │ Ahoj     │
└──────────┴──────────┘
(1 row)

Here is documentation. See Advanced features/Variables section.