Psql API
A series of functions used for interact directly with psql client program or pg_dump client program for manage dumps.
Variables
| Variable |
Description |
|---|
POSTGRESQL_IGNORE_TMZ |
This variable is used to avoid set of timezone on session. |
POSTGRESQL_TMZ |
Timezone to use on session. Default is UTC. |
POSTGRESQL_SHOW_COLUMNS |
If this variable is set then columns are visibile on output
table. Default is hide columns. |
POSTGRESQL_CLIENT |
Path of psql client. |
POSTGRESQL_FORMAT |
Customize format options. Default is unaligned. |
POSTGRESQL_EXTRA_OPTIONS |
Extra options for psql client. |
POSTGRESQL_CLIENT_DUMP |
Path of pg_dump client. |
POSTGRESQL_USER |
Contains Username to use on connection. |
POSTGRESQL_PWD |
Contains password to use on connection. |
POSTGRESQL_DB |
Contains database name to use on connection |
POSTGRESQL_DIR |
Contains path of directory where found project script. |
API
psql_set_auth_var
Set psql_auth variable with arguments to use with psql client program.
Valorize options like: -u username --password=pwd database.
Parameters:
$1: (db) Name of the schema to use$2: (user) User to use on authentication$3: (pwd) Password to use on authentication.$4: (host) Optionally host of the database server.$5: (schema) Optionally schema of the database server.
Returns:
(Show/Hide)psql_set_auth_var () {
local db=$1
local user=$2
local pwd=$3
local host=$4
local schema=$5
local v_host=""
local v_schema=""
if [ ! -z "$host" ] ; then
v_host="-h $host"
fi
if [ ! -z "$schema" ] ; then
v_schema="-v schema=${schema}"
fi
export PGPASSWORD="$pwd"
# TODO: handle ~/.pgpass file
psql_auth="$v_host $v_schema -U $user -w $db"
[[ $DEBUG && $DEBUG == true ]] && echo "Use '$psql_auth'"
return 0
}
psql_cmd_4var
Execute an input statement/command to configured schema.
Variables Used:
POSTGRESQL_IGNORE_TMZ: this variable is used as an alternative to set third parameter
of the function and avoid set of timezone on session.POSTGRESQL_TMZ: timezone to use on session. Default is UTC.POSTGRESQL_SHOW_COLUMNS if this variable is set then columns are visibile on output
table. Default is hide columns.POSTGRESQL_CLIENT: Path of psql client.POSTGRESQL_FORMAT: Customize format options. Default is unaligned.POSTGRESQL_EXTRA_OPTIONS: Extra options for psql client.
Parameters:
$1: (var) Name of the variable where is save output command.$2: (cmd) Command/statement to execute on configured schema$3: (rm_lf) If string length is not zero than from output command are remove LF.$4: (avoid_tmz) Flag to avoid set of timezone on session
(1 to avoid, 0 to leave default). (Optional)
Returns:
0: on success1: on error
(Show/Hide)psql_cmd_4var () {
set -f
local var=$1
local cmd="$2"
local rm_lf=$3
local avoid_tmz=$4
local v=""
local opts=""
# Set separator
opts="${opts} -P fieldsep='|'"
# Disable footer
opts="${opts} -P footer=off"
# Tuple only
opts="${opts} -t" # equals to -P tuples_only=true
# Quiet mode
# opts="${opts} -q"
local tz="
\o /dev/null
SET timezone = '$POSTGRESQL_TMZ';
\o
"
if [[ -n "$avoid_tmz" && x"$avoid_tmz" == x"1" ]] ; then
tz=""
fi
if [[ ! -z "$POSTGRESQL_IGNORE_TMZ" && $POSTGRESQL_IGNORE_TMZ -eq 1 ]] ; then
tz=""
fi
if [ -z "$POSTGRESQL_SHOW_COLUMNS" ] ; then
# Disable columns as default
opts="${opts} -P columns=off"
fi
if [ -z "${POSTGRESQL_FORMAT}" ] ; then
# Format unaligned
opts="${opts} -P format=unaligned"
fi
[[ $DEBUG && $DEBUG == true ]] && echo -en "Connection options: $POSTGRESQL_CLIENT $opts $POSTGRESQL_EXTRA_OPTIONS $psql_auth\n"
v=$($POSTGRESQL_CLIENT $opts $POSTGRESQL_EXTRA_OPTIONS $psql_auth 2>&1 <<EOF
$tz
$cmd;
\q
EOF
)
local ans=$?
[[ $DEBUG && $DEBUG == true ]] && echo -en "$cmd ==> $v ($ans)\n"
if [[ $ans -eq 0 && -n "$v" ]] ; then
if [[ -n $rm_lf ]] ; then
v=`echo $v | sed 's/\n//g'`
fi
fi
# declare -x "$var"="$v"
#read -r "$var" <<< "$v"
eval "$var=\$v"
set +f
return $ans
}
Commons Psql API
commons_psql_check_client
Check if psql client program is present on system.
If present POSTGRESQL_CLIENT variable with abs path is set.
Function check if it is set psql variable:
- if it is not set then try to find path through 'which' program
- if it is set then check if path is correct and program exists.
Returns:
0: on success1: on error
(Show/Hide)commons_psql_check_client () {
if [ -z "$psql" ] ; then
# POST: psql variable not set
tmp=`which psql 2> /dev/null`
var=$?
if [ $var -eq 0 ] ; then
[[ $DEBUG && $DEBUG == true ]] && echo -en "Use psql: $tmp\n"
POSTGRESQL_CLIENT=$tmp
unset tmp
else
error_generate "psql program not found"
return 1
fi
else
# POST: psql variable already set
# Check if file is correct
if [ -f "$psql" ] ; then
[[ $DEBUG && $DEBUG == true ]] && echo -en "Use psql: $psql\n"
POSTGRESQL_CLIENT=$psq
else
error_generate "$psql program invalid."
return 1
fi
fi
export POSTGRESQL_CLIENT
return 0
}
commons_psql_check_client_dump
Check if pg_dump client program is present on system.
If present POSTGRESQL_CLIENT_DUMP variable with abs path is set.
Function check if it is set pg_dump variable:
- if it is not set then try to find path through 'which' program
- if it is set then check if path is correct and program exists.
Returns:
0: on success1: on error
(Show/Hide)commons_psql_check_client_dump () {
if [ -z "$pg_dump" ] ; then
# POST: pg_dump variable not set
tmp=`which pg_dump 2> /dev/null`
var=$?
if [ $var -eq 0 ] ; then
[[ $DEBUG && $DEBUG == true ]] && echo -en "Use pg_dump: $tmp\n"
POSTGRESQL_CLIENT_DUMP=$tmp
unset tmp
else
error_generate "pg_dump program not found"
return 1
fi
else
# POST: pg_dump variable already set
# Check if file is correct
if [ -f "$pg_dump" ] ; then
[[ $DEBUG && $DEBUG == true ]] && echo -en "Use pg_dump: $pg_dump\n"
POSTGRESQL_CLIENT_DUMP=$pg_dump
else
error_generate "$pg_dump program invalid."
return 1
fi
fi
export POSTGRESQL_CLIENT_DUMP
return 0
}
commons_psql_check_vars
Check if are present mandatary psql environment variables:
- POSTGRESQL_USER
- POSTGRESQL_PWD
- POSTGRESQL_DB
- POSTGRESQL_DIR
Returns:
0: all mandatory variables are present.1: on error (program is interrupter with exit 1 command)
(Show/Hide)commons_psql_check_vars () {
local commons_msg='variable on configuration file, through arguments or on current profile.'
check_var "POSTGRESQL_USER" || error_handled "You must define POSTGRESQL_USER $commons_msg"
check_var "POSTGRESQL_PWD" || error_handled "You must define POSTGRESQL_PWD $commons_msg"
check_var "POSTGRESQL_DB" || error_handled "You must define POSTGRESQL_DB $commons_msg"
check_var "POSTGRESQL_DIR" || error_handled "You must define POSTGRESQL_DIR $commons_msg"
return 0
}
commons_psql_check_connection
Check connection to database.
Returns:
0: when connection is ok.1: on error
(Show/Hide)commons_psql_check_connection () {
if [ -z "$POSTGRESQL_CLIENT" ] ; then
return 1
fi
if [ -z "$psql_auth" ] ; then
return 1
fi
[[ $DEBUG && $DEBUG == true ]] && \
echo -en "(commons_psql_check_connection) Try connection with $POSTGRESQL_EXTRA_OPTIONS $psql_auth.\n"
$POSTGRESQL_CLIENT $POSTGRESQL_EXTRA_OPTIONS $psql_auth 2>&1 << EOF
\\q
EOF
errorCode=$?
if [ ${errorCode} -ne 0 ] ; then
return 1
fi
unset errorCode
[[ $DEBUG && $DEBUG == true ]] && echo "psql was connected successfully"
return 0
}
commons_psql_shell
Enter on command line shell of Postgresql server.
Returns:
0: on success1: on error
(Show/Hide)commons_psql_shell () {
local opts=""
local errorCode=""
if [ -z "$POSTGRESQL_CLIENT" ] ; then
return 1
fi
if [ -z "$psql_auth" ] ; then
return 1
fi
# Disable Pagination
opts="-P pager=off"
[[ $DEBUG && $DEBUG == true ]] && \
echo -en "(commons_psql_shell) Try connection with $opts $POSTGRESQL_EXTRA_OPTIONS $psql_auth.\n"
$POSTGRESQL_CLIENT $opts $POSTGRESQL_EXTRA_OPTIONS $psql_auth
errorCode=$?
if [ ${errorCode} -ne 0 ] ; then
return 1
fi
unset errorCode
return 0
}
commons_psql_dump
Dump database or schema from Postgresql server.
Parameters:
$1: (targetfile) File where is stored dump.$2: (onlySchema) If equal 1 then dump save only schema without data.
On default dump save both schema and data. (optional)
Returns:
0: on success1: on error
(Show/Hide)commons_psql_dump () {
local opts=""
local targetfile="$1"
local onlySchema="${2:-1}"
local errorCode=""
if [ -z "$POSTGRESQL_CLIENT_DUMP" ] ; then
return 1
fi
if [ -z "$psql_auth" ] ; then
return 1
fi
if [[ -n "$POSTGRESQL_SCHEMA" && ${onlySchema} -eq 1 ]] ; then
opts="-n ${POSTGRESQL_SCHEMA}"
fi
[[ $DEBUG && $DEBUG == true ]] && \
echo -en "(commons_psql_dump) Try connection with $opts $POSTGRESQL_EXTRA_OPTIONS $psql_auth.\n"
$POSTGRESQL_CLIENT_DUMP $opts $POSTGRESQL_EXTRA_OPTIONS $psql_auth > ${targetfile}
errorCode=$?
if [ ${errorCode} -ne 0 ] ; then
return 1
fi
unset errorCode
return 0
}