Parameters in a postgres utility script

Question

I need to create an utility script to add an item in a postgres database. My initial approach is to have a bash script with a minimum set of non default values and a postgres sql script with the remaining default columns. Item table has 20 columns.

So here is my simplified bash script:

## A unique system identifier for an Item.
ID_ITM='318'
## The description of the Item.
DE_ITM="A description"

psql -U postgres -d MYDB -v id_itm=$ID_ITM de_itm=$DE_ITM -f insertItemPostgres.sql

As you can see it calls the following sql script(simplified):

Insert into AS_ITM (ID_ITM,ID_LN_PRC,ID_ITM_SL_PRC,ID_MRHRC_GP,ID_RU_ITM_SL,ID_DPT_PS,FL_ITM_DSC,FL_ADT_ITM_PRC,NM_BRN,FL_AZN_FR_SLS,LU_ITM_USG,NM_ITM,DE_ITM,TY_ITM,LU_KT_ST,DE_ITM_LNG,FL_ITM_SBST_IDN,LU_CLN_ORD,LU_EXM_TX,FL_VLD_SRZ_ITM)
values (:id_itm,:de_itm,null,'64',null,null,null,null,null,'1',null,null,null,null,'0',null,'0',null,'0','0');

My problem is that to make this work I need to have the string and ids with two pairs of quotes:

DE_ITM="'A description'"

I need to find out how can I pass the parameters in a literal.

I will appreciate any better way to do it, because I know this is not the best and my db scripting skills are not the best. Also I'm using a bash script but I could be just a sql with the non default values that calls the one that has the insert.

Answer 1

If you have psql 9.0 or later, you can try the following:

First you'll need to quote the expansion of your two variables in the shell, like so:

psql -U postgres -d MYDB -v "id_itm=${ID_ITM}" -v "de_itm=${DE_ITM}" -f insertItemPostgres.sql

Then in your SQL you'll need to reference the variables using the following syntax:

INSERT INTO as_itm (id_itm, id_ln_prc, ...)
VALUES (:'id_itm', :'de_itm', ...)

---

Alas, this didn't work for you for some reason. So here's a more old-school approach which should work on all psql versions: Use special bash syntax to double the quotes in your variables.

psql -U postgres -d MYDB -f insertItemPostgres.sql \
    -v "id_itm='${ID_ITM//\'/''}'" \
    -v "de_itm='${DE_ITM//\'/''}'"

In this case the variable references in your SQL should look unchanged from the OP: VALUES (:id_itm, :de_itm, ...

Answer 2

Use a shell HERE-document: shell variables are expanded, even in single quotes.

#!/bin/sh

## A unique system identifier for an Item.
ID_ITM="318"
## The description of the Item.
DE_ITM="A description"

psql -U postgres -d MYDB << THE_END
Insert into AS_ITM(ID_ITM, ID_LN_PRC, ID_ITM_SL_PRC, ID_MRHRC_GP, ID_RU_ITM_SL
   , ID_DPT_PS, FL_ITM_DSC, FL_ADT_ITM_PRC, NM_BRN, FL_AZN_FR_SLS
   , LU_ITM_USG, NM_ITM,DE_ITM, TY_ITM, LU_KT_ST, DE_ITM_LNG
   , FL_ITM_SBST_IDN, LU_CLN_ORD, LU_EXM_TX, FL_VLD_SRZ_ITM)
values ('$ID_TTM', null,'64', null, null
       , null, null, '$DE_ITM' , '1', null
       , null, null, null, '0', null
        , '0', null, '0', '0');

THE_END