How to use a postgres variable in the select clause
SQL has no support for variables, this is only possible in procedural languages (in Postgres that would e.g. be PL/pgSQL).
The way to to this in plain SQL is to use a CTE (which is also a cross platform solution and not tied to any SQL dialect):
with vars (foo) as (
  values ('bar')
)
select foo 
from vars;



like injecting constant values where a select is providing the input to an insert table when copying from a staging table.

Well you don't need a variable for that:
insert into target_table (c1, c2, c3)
select col_1, col_2, 'some value'
from staging_table;