MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Drop Overloaded Routine

without comments

In October 2019, I wrote a post with anonymous block programs to drop tables, sequences, routines, and triggers. Two weeks later, I wrote another post to drop all overloaded routines. However, I recognized the other day that I should have written a function that let you target which function or procedure you want to drop.

The older code only let you drop all of your functions or procedures. That was overkill when you’re working on new functions or procedures.

This post provides a utility for those writing functions and procedures in a public schema of any database in a PostgreSQL installation. It is designed to drop functions or procedures from the public schema.

The code follows below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104

CREATE OR REPLACE
  FUNCTION drop_routine( IN pv_routine_name    VARCHAR(64)
                       , IN pv_routine_type    VARCHAR(64))
  RETURNS INTEGER AS
  $$
  DECLARE
    /* Declare the current catalog. */
    lv_local_catalog  VARCHAR(64) := current_database();
 
    /* Declare return type variable. */
    lv_retval  INTEGER := 1;
 
    /* Manage data dictionary case mechanics:
       ======================================
	     routine_name is always in lowercase.
	     routine_type is always in uppercase. */
    lv_routine_name  VARCHAR(64) := LOWER(pv_routine_name);
    lv_routine_type  VARCHAR(64) := UPPER(pv_routine_type);
 
    /* Declare an indefinite length string for SQL statement. */
    sql  VARCHAR;
 
    /* Declare variables to manage cursor return values. */
    row  RECORD;
    arg  VARCHAR;
 
    /* Declare parameter list. */
    list VARCHAR;
 
    /* Declare a routine cursor. */
    routine_cursor CURSOR( cv_routine_name  VARCHAR
                         , cv_routine_type  VARCHAR ) FOR
      SELECT r.routine_name
      ,      r.specific_name
      ,      r.routine_type
      FROM   information_schema.routines r
      WHERE  r.specific_catalog = current_database()
      AND    r.routine_schema = 'public'
      AND    r.routine_type = cv_routine_type
      AND    r.routine_name = cv_routine_name;
 
    /* Declare a parameter cursor. */
    parameter_cursor CURSOR( cv_specific_name  VARCHAR ) FOR
      SELECT args.data_type
      FROM   information_schema.parameters args
      WHERE  args.specific_catalog = current_database()
      AND    args.specific_schema = 'public'
      AND    args.specific_name = cv_specific_name;
 
  BEGIN
    /* Open the cursor. */
    OPEN routine_cursor(lv_routine_name, lv_routine_type);
    <<row_loop>>
    LOOP
      /* Fetch table names. */
      FETCH routine_cursor INTO row;
 
      /* Exit when no more records are found. */
      EXIT row_loop WHEN NOT FOUND;
 
      /* Initialize parameter list. */
      list := '(';
 
      /* Open the parameter cursor. */
      OPEN parameter_cursor(row.specific_name::varchar);
      <<parameter_loop>>
      LOOP
        FETCH parameter_cursor INTO arg;
 
        /* Exit the parameter loop. */
        EXIT parameter_loop WHEN NOT FOUND;
 
        /* Add parameter and delimit more than one parameter with a comma. */
        IF LENGTH(list) > 1 THEN
          list := CONCAT(list,',',arg);
        ELSE
          list := CONCAT(list,arg);
        END IF;
      END LOOP;
 
      /* Close the parameter list. */
      list := CONCAT(list,')');
 
      /* Close the parameter cursor. */
      CLOSE parameter_cursor;
 
      /* Concatenate together a DDL to drop the table with prejudice. */
      sql := 'DROP '||row.routine_type||' IF EXISTS '||row.routine_name||list;
 
      /* Execute the DDL statement. */
      EXECUTE sql;
 
      /* Assign success flag of 0. */
      lv_retval := 0;
 
    END LOOP;
 
    /* Close the routine_cursor. */
    CLOSE routine_cursor;
 
    /* Return the output text variable. */
    RETURN lv_retval;
  END
  $$ LANGUAGE plpgsql;

If you now create a series of hello overloaded functions, like:

CREATE OR REPLACE
  FUNCTION hello()
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT 'Hello World!' INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;
 
CREATE OR REPLACE
  FUNCTION hello(whom text)
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT CONCAT('Hello ',whom,'!') INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;
 
CREATE OR REPLACE
  FUNCTION hello(id int, whom text)
  RETURNS text AS
  $$
  DECLARE
    output  VARCHAR;
  BEGIN
    SELECT CONCAT('[',id,'] Hello ',whom,'!') INTO output;
    RETURN output;
  END
  $$ LANGUAGE plpgsql;

After you create the overloaded functions, you can query their status from the information_schema.routines table in the data dictionary:

SELECT routine_name
,      specific_name
,      routine_type
FROM   information_schema.routines
WHERE  specific_catalog = current_setting('videodb.catalog_name')
AND    routine_schema = 'public'
AND    routine_name = 'hello';

Which shows you the three versions of the hello function:

 routine_name | specific_name | routine_type
--------------+---------------+--------------
 hello        | hello_18100   | FUNCTION
 hello        | hello_18101   | FUNCTION
 hello        | hello_18102   | FUNCTION
(3 rows)

You can drop all versions of the hello functions by calling the drop_routine function:

SELECT CASE
         WHEN drop_routine('hello','function') = 0
         THEN 'Success'
         ELSE 'Failure'
       END AS drop_routine;

It returns the following:

 drop_routine
--------------
 Success
(1 row)

As always, I hope this helps those looking for how to routinely test new functions and procedures.

Written by maclochlainn

March 6th, 2022 at 11:53 pm

Posted in Fedora,Linux,Microsoft Windows 10,PL/pgSQL,Postgres,PostgreSQL,PostgreSQL 14,Unix,Windows 10,Windows OS

«
»