How to create a PL/pgSQL function in a database using a bash script?

Hello

Thanks for reaching out on Microsoft Q&A!

The key issue is that your script always installs functions into the postgres database (the default maintenance DB), while you’ve been testing manually against your application database. On Azure Database for PostgreSQL, functions are schema-bound and must be created in the target application DB, not in postgres.

You’re connecting to -d postgres but you said the function and users table live in end_db

Here's a corrected Bash script:

• Explicitly uses the right database (change DB to actual DB).

Enforces sslmode=require (mandatory on Azure).

Surfaces errors by removing quiet flags when installing functions.

Logs properly, with least-privilege file permissions

#!/bin/bash
# Paths to log files
LOG_OK="/appl/mydir/logs/success_change_password.log"
LOG_ERR="/appl/mydir/logs/error_change_password.log"
# Clear log files at the beginning of the script and add a timestamp
timestamp=$(date '+%Y-%m-%d %H:%M:%S')
echo "### SCRIPT EXECUTION LOG FOR PASSWORD CHANGE FROM $timestamp" > "$LOG_OK"
echo "### ERROR LOG FOR SCRIPT EXECUTION OF PASSWORD CHANGE FROM $timestamp" > "$LOG_ERR"
# Function to install pg functions into the database
run_sql_commands() {
    local user=$1
    local pass=$2
    local id=$3
    local host=$4
    local port=$5
    local login=$6
    local password=$7
    local dbname="end_db"   # <-- CHANGE THIS to the correct target DB name
    # Install first function
    PGPASSWORD="$pass" /usr/bin/psql \
      "host=$host port=$port user=$user dbname=$dbname sslmode=require" \
      -v ON_ERROR_STOP=1 \
      -f /appl/mydir/first_function.sql \
      >> "$LOG_OK" 2>> "$LOG_ERR"
    # Install second function
    PGPASSWORD="$pass" /usr/bin/psql \
      "host=$host port=$port user=$user dbname=$dbname sslmode=require" \
      -v ON_ERROR_STOP=1 \
      -f /appl/mydir/second_function.sql \
      >> "$LOG_OK" 2>> "$LOG_ERR"
    # Call function and capture output
    update_sql="SELECT F_PG_CHANGE_PASSWORD($id, '$login', '$password');"
    update_var=$(PGPASSWORD="$pass" /usr/bin/psql \
      "host=$host port=$port user=$user dbname=$dbname sslmode=require" \
      -AXqtc "$update_sql" 2>> "$LOG_ERR")
    echo "$update_var" > /appl/mydir/update_string.sql
    chmod 640 /appl/mydir/update_string.sql
}
# Read arguments from var_select.txt
while read -r i j k l m; do
    id="$i"
    host="$j"
    port="$k"
    login="$l"
    password="$m"
    # load user info and password for DB connection
    while IFS='=' read -r key value; do
        case "$key" in
            "USER2") user="$value" ;;
            "PASSWORD") pass="$value" ;;
            "USER4") user2="$value" ;;
            "PASSWORD4") pass2="$value" ;;
        esac
    done < /appl/mydir2/.env
    if [ -n "$user" ] && [ -n "$pass" ]; then
        echo "[$(date '+%Y-%m-%d %H:%M:%S')] Connecting to host: $host, port: $port with USER2" >> "$LOG_OK"
        run_sql_commands "$user" "$pass" "$id" "$host" "$port" "$login" "$password"
    elif [ -n "$user2" ] && [ -n "$pass2" ]; then
        echo "[$(date '+%Y-%m-%d %H:%M:%S')] Connecting to host: $host, port: $port with USER4" >> "$LOG_OK"
        run_sql_commands "$user2" "$pass2" "$id" "$host" "$port" "$login" "$password"
    else
        echo "[$(date '+%Y-%m-%d %H:%M:%S')] User or password not found in .env file" >> "$LOG_ERR"
    fi
done < /appl/mydir/var_select.txt
# Run exported SQL against end_db
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Connecting to localhost:5432 to update table mytable" >> "$LOG_OK"
/usr/bin/psql "host=localhost port=5432 user=postgres dbname=end_db sslmode=prefer" \
    -f /appl/mydir/update_string.sql >> "$LOG_OK" 2>> "$LOG_ERR"
if [ $? -eq 0 ]; then
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] SQL script execution completed successfully" >> "$LOG_OK"
else
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] Error executing sql file update_string.sql" >> "$LOG_ERR"
fi
# Clean up
rm -f "/appl/mydir/var_select.txt" 2>> "$LOG_ERR"
rm -f "/appl/mydir/update_string.sql" 2>> "$LOG_ERR"

1. Connect manually to the target database:

      psql -h <host> -U <user> -d <Target db>
   

2. Check if functions are installed
3. Review $LOG_ERR for any suppressed errors.

Could you please try running the script once and let me know if you still face any issues? I’ll be happy to help further. If you find this information helpful, kindly consider clicking Upvote so it can assist others as well.

Thanks,

Kalyani

Hello Diogo dos Santos,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you would like to create a PL/pgSQL function in a database using a bash script.

Review the below: Open the canvas file titled copy the script to the server (it contains comments & instructions).

#!/usr/bin/env bash
# Improved installer / executor for PL/pgSQL functions (Azure-aware)
# - Adds robust error handling
# - Uses ON_ERROR_STOP so psql returns non-zero on SQL errors
# - Connects to the target application database (not `postgres` by default)
# - Validates function installation and the returned SQL before executing it
set -u
# Logging
LOG_OK="/appl/mydir/logs/success_change_password.log"
LOG_ERR="/appl/mydir/logs/error_change_password.log"
timestamp() { date '+%Y-%m-%d %H:%M:%S'; }
echo "### SCRIPT EXECUTION LOG FOR PASSWORD CHANGE FROM $(timestamp)" > "$LOG_OK"
echo "### ERROR LOG FOR SCRIPT EXECUTION OF PASSWORD CHANGE FROM $(timestamp)" > "$LOG_ERR"
# Path to psql binary
PSQL_BIN="/usr/bin/psql"
# Run SQL files (install functions) and call the change-password function
# Parameters:
# 1 = db_user, 2 = db_pass, 3 = id, 4 = host, 5 = port, 6 = dbname, 7 = login, 8 = password
run_sql_commands() {
    local user="$1"; local pass="$2"; local id="$3"; local host="$4";
    local port="$5"; local dbname="$6"; local login="$7"; local password="$8"
    # Install function SQL files into the target database
    for sqlfile in "/appl/mydir/first_function.sql" "/appl/mydir/second_function.sql"; do
        echo "[$(timestamp)] Installing $sqlfile to $host:$port/$dbname" >> "$LOG_OK"
        PGPASSWORD="$pass" "$PSQL_BIN" -v ON_ERROR_STOP=1 -P pager=off -h "$host" -p "$port" -U "$user" -d "$dbname" -f "$sqlfile" >> "$LOG_OK" 2>> "$LOG_ERR" || {
            echo "[$(timestamp)] ERROR: Failed to install $sqlfile on $host:$port/$dbname" >> "$LOG_ERR"
            return 1
        }
    done
    # Quick verification: ensure the installed function exists in the target DB
    func_exists=$(PGPASSWORD="$pass" "$PSQL_BIN" -A -t -h "$host" -p "$port" -U "$user" -d "$dbname" -c \
        "SELECT n.nspname||'.'||p.proname||pg_get_function_identity_arguments(p.oid) FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE p.proname = 'f_pg_change_password' LIMIT 1;" 2>>"$LOG_ERR")
    if [[ -z "${func_exists// }" ]]; then
        echo "[$(timestamp)] ERROR: function f_pg_change_password not found after installation on $host:$port/$dbname" >> "$LOG_ERR"
        return 1
    else
        echo "[$(timestamp)] Installed function found: $func_exists" >> "$LOG_OK"
    fi
    # Call the function and capture its output (expecting a valid SQL statement or statements)
    # Use dollar-quoting so embedded single-quotes in $login/$password won't break the command
    update_var=$(PGPASSWORD="$pass" "$PSQL_BIN" -A -t -v ON_ERROR_STOP=1 -h "$host" -p "$port" -U "$user" -d "$dbname" -c "SELECT F_PG_CHANGE_PASSWORD($id, $$${login}$$, $$${password}$$);" 2>>"$LOG_ERR") || {
        echo "[$(timestamp)] ERROR: calling function F_PG_CHANGE_PASSWORD on $host:$port/$dbname" >> "$LOG_ERR"
        return 1
    }
    # Validate the function output
    if [[ -z "${update_var// }" ]]; then
        echo "[$(timestamp)] ERROR: function returned empty output (no SQL to run)" >> "$LOG_ERR"
        return 1
    fi
    # Ensure the returned SQL ends with a semicolon (so psql can execute it safely)
    if ! printf '%s' "$update_var" | grep -q ';'; then
        update_var="$update_var;"
    fi
    # Write the SQL safely to the update file (use safe permissions)
    printf '%s
' "$update_var" > /appl/mydir/update_string.sql
    chmod 600 /appl/mydir/update_string.sql
    echo "[$(timestamp)] Wrote update SQL to /appl/mydir/update_string.sql" >> "$LOG_OK"
    return 0
}
# Main loop: we expect var_select.txt lines to include database name as the 6th column
# Format expected for /appl/mydir/var_select.txt:
# id host port dbname login password
while read -r i j k l m n; do
    id="$i"; host="$j"; port="$k"; dbname="$l"; login="$m"; password="$n"
    # load DB credentials from .env
    while IFS='=' read -r key value; do
        case "$key" in
            "USER2") user="$value" ;;
            "PASSWORD") pass="$value" ;;
            "USER4") user2="$value" ;;
            "PASSWORD4") pass2="$value" ;;
        esac
    done < /appl/mydir2/.env
    # choose credentials
    if [[ -n "${user:-}" && -n "${pass:-}" ]]; then
        echo "[$(timestamp)] Connecting to $host:$port (db=$dbname) with on-prem creds" >> "$LOG_OK"
        run_sql_commands "$user" "$pass" "$id" "$host" "$port" "$dbname" "$login" "$password" 2>> "$LOG_ERR"
        if [[ $? -eq 0 ]]; then
            echo "[$(timestamp)] SUCCESS installing and generating SQL for $host:$port/$dbname" >> "$LOG_OK"
        else
            echo "[$(timestamp)] ERROR during operations on $host:$port/$dbname" >> "$LOG_ERR"
        fi
    elif [[ -n "${user2:-}" && -n "${pass2:-}" ]]; then
        echo "[$(timestamp)] Using Azure creds for $host:$port (db=$dbname)" >> "$LOG_OK"
        run_sql_commands "$user2" "$pass2" "$id" "$host" "$port" "$dbname" "$login" "$password" 2>> "$LOG_ERR"
        if [[ $? -eq 0 ]]; then
            echo "[$(timestamp)] SUCCESS installing and generating SQL for $host:$port/$dbname" >> "$LOG_OK"
        else
            echo "[$(timestamp)] ERROR during operations on $host:$port/$dbname" >> "$LOG_ERR"
        fi
    else
        echo "[$(timestamp)] User or password not found in .env file" >> "$LOG_ERR"
    fi
done < /appl/mydir/var_select.txt
# Execute the generated update statements against local DB (end_db)
if [[ -f /appl/mydir/update_string.sql ]]; then
    echo "[$(timestamp)] Executing /appl/mydir/update_string.sql on localhost:5432 end_db" >> "$LOG_OK"
    /usr/bin/psql -v ON_ERROR_STOP=1 -P pager=off -q -h localhost -p 5432 -U postgres -d end_db -f /appl/mydir/update_string.sql >> "$LOG_OK" 2>> "$LOG_ERR"
    if [[ $? -eq 0 ]]; then
        echo "[$(timestamp)] update_string.sql executed successfully" >> "$LOG_OK"
    else
        echo "[$(timestamp)] ERROR executing update_string.sql" >> "$LOG_ERR"
    fi
    rm -f /appl/mydir/update_string.sql 2>> "$LOG_ERR"
else
    echo "[$(timestamp)] No update_string.sql found (nothing to execute)" >> "$LOG_OK"
fi
# Cleanup
rm -f /appl/mydir/var_select.txt 2>> "$LOG_ERR"
# EOF

• Modify your /appl/mydir/var_select.txt lines to include the database name (format suggested in the script header).
• Run the script on a single host (non-production) and inspect $LOG_ERR.
• If you still see “function not found” after the script succeeds, run the pg_proc query I gave in the verification checklist (it shows schema and exact name).

References for more details:

• https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-servers
• https://www.postgresql.org/docs/current/sql-createfunction.html
• https://www.postgresql.org/docs/current/app-psql.html
• https://www.postgresql.org/docs/current/sql-do.html
• https://learn.microsoft.com/en-us/azure/postgresql/extensions/concepts-extensions-considerations

I hope this is helpful! Do not hesitate to let me know if you have any other questions or clarifications.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Hi, Kalyani Kondavaradala, thank you for answering my question, but I have really tried to install the functions in the Postgres database.

These functions are useful just to change some user's password, and as users are shared by all databases in Postgres, I decided to install them in the Postgres database.

When I try to connect to the second database "end_db", I am trying to update a table in this database with the result of the functions ran in the Postgres database. My functions should live in the server that required some user's password change, and the update about the password change is done in another database in another server.

I have used the Postgres database to handle the users's password change, because this database exists in every cluster in every server.

Hello Diogo dos Santos,

Thank you so much for taking the time to explain your setup and the reasoning behind placing the functions in the Postgres database. Your explanation about how the functions manage password changes across clusters and how you’re updating the other database really helps clarify the scenario. I appreciate you sharing these details, it gives me a much better understanding of your approach.

Can you try executing the above Bash script that i have shared in my previous response, just change this line

local dbname="end_db" # <-- CHANGE THIS to the correct target DB name
to 
local dbname="postgres"

Kindly share the outcome; in case it’s not working as expected, we’ll help you with the correct way forward.

Thanks,

Kalyani

Hi Kalyani Kondavaradala,

I tried it, and the script runs smoothly, however the functions are not installed. The logs don't keep any successful nor failure information about the installation. They only say that the connection to the database and the script execution were successful, and that the connection to the database using the on-prem credentials failed, because it is not an on-prem database.