How to save a Redshift SELECT atribute into a script variable [duplicate]

Question

I want to create a script to automate some processes with Redshift. Specifically, I want find an attribute of one of my tables with a SELECT and then use it in a INSERT. My script looks like this:

psql -h ... -c "SELECT id_process FROM process WHERE de_process = 'EMR'"
psql -h ... -c "INSERT INTO execution (id_process) values (X);"

In the first sentence I get a unique value, the ID I'm looking for, in a format like this:

id_proceso
------------
      2
(1 row)

Then I would like to use it as the value to insert in the second sentence, substituting the "X, but I don't know how to save into a variable and then reuse the output of the first sentence.

Any suggestion?

P.D. In other question it shows how to do it in a unique sentence, but I need to save the value for a future use.

Answer 1

Check psql options, but sample script can be the following:

psql -h localhost -d testdb <<EOF
  \out sample.txt
  \pset border 1
  WITH test_data AS ( SELECT 2 AS id_process)
  SELECT id_process FROM test_data;
  \out
EOF

Result for cat sample.txt will be:

 id_process 
------------
          2
(1 row)

If you want to get just pure value from SELECT statement, consider following params in the example above:

\t:

Toggles the display of output column name headings and row count footer. This command is equivalent to \pset tuples_only and is provided for convenience.

\pset format unaligned:

unaligned format writes all columns of a row on one line, separated by the currently active field separator. This is useful for creating output that might be intended to be read in by other programs (for example, tab-separated or comma-separated format).