Customizing the PSQL prompt with environment variables using value-dependant coloring

[Thomas Boussekey <thomas.boussekey@xxxxxxxxx>]

Hello everyone,

I'm trying to create a custom PSQL prompt with a part of it colored in function of its value.

To be more explicit, we are using a physical replication and I'd like to display in the prompt "MASTER" in red or "slave" in green, when we are connected on the database server.

# Current configuration

In our current configuration, we are using **bash_profile** to create functions to get context dependent information:

```sh

# Utility functions used to build the psql prompt
sql_host() {
    echo ${HOSTNAME%%.*}
}

sql_replication_status() {
    ( [ -f ${PGDATA}/recovery.conf ] && echo "slave" ) || echo "master"
}

export -f sql_host sql_replication_status

```

And the PSQL prompt is build thanks to these variable into the **~/.psqlrc** file:

```sh

\set PROMPT1 '%`sql_host` (%[%033[31;1m%]%`sql_replication_status`%[%033[0m%])=#'

```

It works fine and uses the C coloring method described into the PSQL documentation: https://www.postgresql.org/docs/9.5/app-psql.html#APP-PSQL-PROMPTING

# Tests performed

I tried to create a new function into the **bash_profile** file sending result in a "colored formatted" way, using both shell or psql synthax, here is an extract of my tests:

```sh

export RED_pg="%[%033[1;31m%]"
export NOCOLOR_pg="%[%033[0m%]"

export RED_s="\033[1;31m"
export NOCOLOR_s="\033[0m"

export RED_e=`echo -e '\033[1;31m'`
export NOCOLOR_e=`echo -e '\033[0m'`

sql_prompt_colored_s() {
    case "`sql_replication_status`" in
    "slave" )
      echo "${YELLOW_s}`sql_replication_status`${NOCOLOR_s}";;
    "MASTER" )
      echo "${RED_s}`sql_replication_status`${NOCOLOR_s}";;
  esac
}
sql_prompt_colored_e() {
    case "`sql_replication_status`" in
    "slave" )
      echo "${YELLOW_e}`sql_replication_status`${NOCOLOR_e}";;
    "MASTER" )
      echo "${RED_e}`sql_replication_status`${NOCOLOR_e}";;
  esac
}
sql_prompt_colored_pg() {
    case "`sql_replication_status`" in
    "slave" )
      echo "${YELLOW_pg}`sql_replication_status`${NOCOLOR_pg}";;
    "MASTER" )
      echo "${RED_pg}`sql_replication_status`${NOCOLOR_pg}";;
  esac
}

export -f sql_prompt_colored_s sql_prompt_colored_e sql_prompt_colored_pg

```

I managed to get different output, like: %[%033[1;31m%]MASTER%[%033[0m%] for the function `sql_prompt_colored_pg`.

Yet, when I define a prompt using this variable `sql_prompt_colored_pg`, example:

```sh

\set PROMPT1 '%`sql_host` (%`sql_prompt_colored_pg`)=#'

```

I get the following prompt:

```

mydatabase (%[%033[1;31m%]MASTER%[%033[0m%])=#

```

And when I try to put this exact prompt value into the PROMPT1:

```

\set PROMPT1 '%`sql_host` (%[%033[1;31m%]MASTER%[%033[0m%])=#'

```

I get the expected prompt:

```

mydatabase (MASTER)=#

```

With MASTER in **red colour**.

# Get a working configuration

## Is there another way to make it work?

I tried other ways of implementing this with psql variable for example, but I didn't get a better result.

> Do you know a way to perform this context coloring?

## Analysis of the PSQL source code

I think that the file were everything happens is this one:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/bin/psql/prompt.c;hb=HEAD#

I'm not at ease with C language, so it is very hard to read.

My intuition is that the readline part operation is applied before the shell variable interpolation, is it correct?

> Is the message in this file: **(those will not be rescanned for more escape sequences!)** is present into the file, to tell that no further interpolation can be performed into both Psql and shell variables?

Thanks in advance for your feedbacks and/or workarounds,

Have a nice day,

kind regards,

Thomas