Do you need someone to make some patch on PostgreSQL running on Linux and that person is only able to “run script” but otherwise you cannot rely on anything? Try this solution. It is not genius but it is sufficient.

PostgreSQL sql file – I use name “patch_.sql”

--we create new function for patch
CREATE OR REPLACE FUNCTION config."patch_123"()
  RETURNS void AS
$BODY1$
declare
   --... if you need some variables
BEGIN

-----------------------------------------------------------------------------------------------------------

--here we can modify tables / views etc.

-----------------------------------------------------------------------------------------------------------

--we can also insert / update functions
-- we just need to use different string between $$ - to distinguish it from main function body
CREATE OR REPLACE FUNCTION some_function
    LANGUAGE plpgsql
    AS $_$
.......... function body.....
END
$_$;

-----------------------------------------------------------------------------------------------------------

END;
$BODY1$
LANGUAGE plpgsql VOLATILE COST 100;

-- call patch procedure after import
SELECT config."patch_123"();

-- if patch fails next command is not executed so we have a rollback and see what has happened
DROP FUNCTION config."patch_123"();

 

 

Linux bash script for update:


#!/bin/bash

echo ""
echo "Patch for ....."
echo "========================================="

cmdexist=$(command -v psql|wc -l)
if [ $cmdexist == 0 ]; then
    echo "program psql not found"
    exit 1
fi

#if you need to check version of application - do it now
rightver="2.345678"
##I presume you have some table to keep version number...
appver=$(psql -U postgres -d nrs -t -c "select version_number from data.appversion order by appdate desc limit 1")
if [ $? == 0 ]; then
    if [ $(echo "${appver} != ${rightver}"|bc) == 1 ]; then  ##here test for version
        echo "Found version ${appver}"
        echo "This hotfix is intended for version ${rightver}"
        exit 1
    fi
else
    echo "Application not found"
    exit 1
fi

##procedure to implement patch
runpatch() {
dbexist=$(psql -U postgres -d postgres -t -c "select count(*) from pg_database where datname='${dbname}'")
if [ `echo "${dbexist} > 0"|bc` -eq 1 ]; then
    echo "Implementing hotfix on database $dbname..."
    psql -U postgres -d ${dbname} -f patch_${dbname}.sql   ##check your file name
    if [ $? != 0 ]; then
        echo "Error in patch on database $dbname"
        exit 1
    fi
    echo "$dbname DONE"
    echo ""
fi
}

##here you can have more of these calls for different dbs or cycle
dbname=mydatabase
runpatch

echo ""
echo "All DONE"
echo ""