PostgreSQL – SET CONNECTION Error ‘Unrecognized Configuration Parameter’

connectionspostgresql

According to the PostgreSQL documentation, there appears to be support for SET CONNECTION.

However, when I try to execute it from the psql prompt, I get

ERROR: unrecognized configuration parameter "connection"

I'm well aware of using \connect but I want to be able to switch databases within the execution of a .sql file.

Is this a bug in PostgreSQL? Or does some flag need to be set to create the parameter?

Best Answer

Converting question comments into an answer

Abelisto:

It seems that you missed a section of the documentation: Chapter 34. ECPG - Embedded SQL in C. The PostgreSQL documentation is not ideal (but IMO it is much better then many others). Hint: to avoid inconveniences look at the page name in the address, for example plpgsql-declarations.html is about DECLARE variables in the plpgsql blocks and sql-declare.html is about DECLARE cursors in the pure SQL.

dezso:

If using psql, you still can make use of \c - especially if it does not require typing a password.

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

Postgresql – TimeZone parameter in postgresql.conf

Like with any other parameter that can be defined for every session, the setting in postgresql.conf is the default in the absence of an explicit setting for the session.

Best Answer

Related Solutions

Postgresql – Span single transaction over several sequential psql sessions

Postgresql – TimeZone parameter in postgresql.conf

Related Question