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.

Step-by-step guide

1. 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.

2. 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

3. 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.