RE: Backup Functions

Hey Vijaykumar,
Thank you for the quick response.

Q) do you see the functions expected to be dumped under \df public.* of the db ?
Response) Yes, I do see all the functions listed.

However, when tried to dump it does not include all functions.
--
-- PostgreSQL database dump
--

-- Dumped from database version 11.10
-- Dumped by pg_dump version 11.10

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: xxx
--

CREATE SCHEMA public;

ALTER SCHEMA public OWNER TO xxx;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: xxx
--

COMMENT ON SCHEMA public IS 'standard public schema';

--
-- Name: dmc_update_row_modified_function(); Type: FUNCTION; Schema: public; Owner: dbmsowner
--

CREATE FUNCTION public.dmc_update_row_modified_function() RETURNS trigger
LANGUAGE plpgsql
AS $$ BEGIN IF TG_OP = 'UPDATE' THEN NEW.modified_dttm = NOW(); RETURN NEW; ELSIF TG_OP = 'INSERT' THEN NEW.created_dttm = NOW(); RETURN NEW; END IF; END; $$;

ALTER FUNCTION public.dmc_update_row_modified_function() OWNER TO dbmsowner;

--
-- PostgreSQL database dump complete

From: Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>
Sent: Wednesday, August 18, 2021 11:30 AM
To: Sridhar Parepalli <Sridhar(dot)Parepalli(at)sas(dot)com>
Cc: pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org>
Subject: Re: Backup Functions

EXTERNAL

On Wed, 18 Aug 2021 at 20:35, Sridhar Parepalli <Sridhar(dot)Parepalli(at)sas(dot)com<mailto:Sridhar(dot)Parepalli(at)sas(dot)com>> wrote:

I know in the public schema of the Tenant database there are over 60 functions and are not backed up. Any pointers are greatly helpful.

dumping only objects in a specific schema, also dumped functions in that schema, at least in my demo run.

postgres(at)db:~/playground$ psql test
psql (14beta1)
Type "help" for help.

test=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-------------------------+--------------------------+-----------------------------------------+------
public | get_rounded_up_interval | timestamp with time zone | ts timestamp with time zone, i interval | func
(1 row)

test=# \q
postgres(at)db:~/playground$ pg_dump -n public test > /tmp/test.sql
postgres(at)db:~/playground$ grep -A5 get_rounded_up_interval /tmp/test.sql
-- Name: get_rounded_up_interval(timestamp with time zone, interval); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.get_rounded_up_interval(ts timestamp with time zone, i interval) RETURNS timestamp with time zone
LANGUAGE plpgsql
AS $$
begin
return date_bin(i, ts, date_trunc('day', ts)) + i;
end; $$;
--
ALTER FUNCTION public.get_rounded_up_interval(ts timestamp with time zone, i interval) OWNER TO postgres;

***********
I also tried using -Fd and then checked via. i do see functions dumped.

pg_dump -Fd -d test -n public -f /tmp/test.dump
pg_restore -l /tmp/test.dump | grep -i function
219; 1255 72283 FUNCTION public get_rounded_up_interval(timestamp with time zone, interval) postgres

do you see the functions expected to be dumped under \df public.* of the db ?
--
Thanks,
Vijay
Mumbai, India