Extract Non-System Functions from the iShare Database

Overview

You may wish to extract all Non-System Functions from your iShare Database in order to keep a backup. This topic is a guide to creating and running a script to be run over the public schema of the supplied Database name. The script will extract the information to a .txt file which may then be used to recreate the functions if required. If you have any bespoke functions on your iShare database then it would be good practice to use this script as a backup. It is always a good idea to backup these functions before running the AstunPGInstaller to upgrade your iShare database.

If you wish to keep an off-site backup this .txt file could always be uploaded to the Astun FTP site. Please raise a Support Request if you do not have access to the Astun FTP.

This script will only extract the Functions from your iShare Database, if you wish to backup the entire database (including the Functions) please see Backing up your Database for details.

Step-by-step guide

Save the following code to a file called grab_all_functions.sql.

grab_all_functions.sql

SELECT pg_get_functiondef(f.oid) as "definition"
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'public'
AND proname not like 'pg%'
AND proname not like '_st_%'
AND proname not like 'st_%'
AND proisagg = false
ORDER BY nspname, proname;

This is trying to exclude the PostgreSQL System Functions where possible.

Open a Command prompt and run the following command:
```
"C:\Program Files (x86)\PostgreSQL\9.0\bin\psql.exe" -f [sql_file] -h [server_name] -U postgres -d [database_name] -o [output_file]
```
where:
sql_file is the path to the file saved above e.g. d:\grab_all_functions.sql
server_name is the name or IP address of the server e.g. 127.0.0.1
database_name is the name of the database as it appears in PGAdmin e.g. iShareData_Astun
output_file is the path to the filename where you would like the output saved to e.g. d:\output.txt
Example
```
"C:\Program Files (x86)\PostgreSQL\9.0\bin\psql.exe" -f d:\grab_all_functions.sql -h 127.0.0.1 -U postgres -d iShareData_Astun -o d:\output.txt
```
Once run, you will be prompted for the postgres users password. For this reason you will not be able to configure this to run under Workflow.