{"id":8359,"date":"2023-06-29T09:46:42","date_gmt":"2023-06-29T14:46:42","guid":{"rendered":"https:\/\/bobbeaty.com\/wp\/?p=8359"},"modified":"2023-07-02T08:14:51","modified_gmt":"2023-07-02T13:14:51","slug":"nice-postgresql-trick","status":"publish","type":"post","link":"https:\/\/bobbeaty.com\/wp\/archives\/8359","title":{"rendered":"Nice Postgresql Trick"},"content":{"rendered":"

\"PostgreSQL.jpg\"<\/p>\n

This morning I really wanted to be able to set the psql<\/tt> prompt in my sessions because the system we have for creating databases doesn't really create nicely human-readable<\/em> name, and even so, I'd like to have the name in the prompt match the branch of the code I'm working on... it just works out better.<\/p>\n

So I started digging, and the -c<\/tt> parameter is OK, but the psql<\/tt> session terminates after that - so that's not going to work. And piping in the \\set<\/tt> commands seemed to be problematic, and then I found this:<\/p>\n

  $ psql --set<\/span>=PROMPT1<\/span>="${br}<\/span>%R%#%x "<\/span><\/pre>\n
where br<\/tt> is the name of the branch I'm working on. This could then be obtained from git<\/tt> easily, and then put into a function, and it works great!<\/p>\n
  #<\/span>\n  # "platter psql (postgres command line tool)" - start a psql session on<\/span>\n  # the provided branch in the PLATTER_INSTANCE in the .env file in the<\/span>\n  # current directory (a Node project repo). If no branch is provided, then<\/span>\n  # the current git branch will be used.<\/span>\n  #<\/span>\n  function<\/span> ppsql(<\/span>)<\/span> {<\/span>\n    if<\/span> [<\/span> -f<\/span> .env ]<\/span>; then<\/span>\n      set<\/span> -o<\/span> allexport; source<\/span> .env; set<\/span> +o allexport\n      local<\/span> br<\/span>=$1<\/span>\n      if<\/span> [<\/span> ${#br}<\/span> -eq<\/span> 0<\/span> ]<\/span>; then<\/span>\n        br<\/span>=`<\/span>cat<\/span> .git\/<\/span>HEAD |<\/span> sed<\/span> -e<\/span> 's:^.*\/::'<\/span>`<\/span>\n      fi<\/span>\n      local<\/span> url<\/span>="`npx platter postgres branch url ${br} \\\n           --instance $PLATTER_INSTANCE | tr -d '\\n'`<\/span>?sslmode=require"<\/span>\n      psql --set<\/span>=PROMPT1<\/span>="${br}<\/span>%R%#%x "<\/span> --set<\/span>=PROMPT2<\/span>="${br}<\/span>%R%#%x "<\/span> $url<\/span>\n    else<\/span>\n      echo<\/span> "Not in a Node project directory!"<\/span>\n    fi<\/span>\n  }<\/span><\/pre>\n
With this, it's so easy now to be able to keep track of the database (branch) I'm on with Platter, and that makes a really big different to my peace of mind. \ud83d\ude42 <\/p>\n","protected":false},"excerpt":{"rendered":"
This morning I really wanted to be able to set the psql prompt in my sessions because the system we have for creating databases doesn’t really create nicely human-readable name, and even so, I’d like to have the name in the prompt match the branch of the code I’m working on… it just works out […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4],"tags":[],"class_list":["post-8359","post","type-post","status-publish","format-standard","hentry","category-coding","category-open-source-software"],"_links":{"self":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/8359","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/comments?post=8359"}],"version-history":[{"count":2,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/8359\/revisions"}],"predecessor-version":[{"id":8361,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/8359\/revisions\/8361"}],"wp:attachment":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/media?parent=8359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/categories?post=8359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/tags?post=8359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}