MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Postgres Overloaded Routines

without comments

Earlier I showed how to write an anonymous block in PostgreSQL PL/pgSQL to drop routines, like functions and procedures. However, it would only work when they’re not overloaded functions or procedures. The following lets you drop all routines, including overloaded functions and procedures. Overloaded procedures are those that share the same name but have different parameter lists.

Before you can test the anonymous block, you need to create a set of overloaded functions or procedures. You can create a set of overloaded hello procedures with the following syntax:

CREATE FUNCTION hello()
RETURNS text AS
$$
DECLARE
  output  VARCHAR;
BEGIN
  SELECT 'Hello World!' INTO output;
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
CREATE FUNCTION hello(whom text)
RETURNS text AS
$$
DECLARE
  output  VARCHAR;
BEGIN
  SELECT CONCAT('Hello ',whom,'!') INTO output;
  RETURN output;
END
$$ LANGUAGE plpgsql;
 
CREATE 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;

You can test the overloaded hello function, like so from the videodb schema:

videodb=> SELECT hello();
    hello     
--------------
 Hello World!
(1 ROW)
 
videodb=> SELECT hello('Captain Marvel');
         hello         
-----------------------
 Hello Captain Marvel!
(1 ROW)
 
videodb=> SELECT hello(1,'Captain America');
           hello            
----------------------------
 [1] Hello Captain America!
(1 ROW)

Then, you can query the information_schema to verify that you’ve created a set of overloaded procedures with the following query:

SELECT   proc.specific_schema AS procedure_schema
,        proc.specific_name
,        proc.routine_name AS procedure_name
,        proc.external_language
,        args.parameter_name
,        args.parameter_mode
,        args.data_type
FROM     information_schema.routines proc left join information_schema.parameters args
ON       proc.specific_schema = args.specific_schema
AND      proc.specific_name = args.specific_name
WHERE    proc.routine_schema NOT IN ('pg_catalog', 'information_schema')
AND      proc.routine_type IN ('FUNCTION','PROCEDURE')
ORDER BY procedure_schema
,        specific_name
,        procedure_name
,        args.ordinal_position;

It should return the following:

 procedure_schema | specific_name | procedure_name | external_language | parameter_name | parameter_mode | data_type 
------------------+---------------+----------------+-------------------+----------------+----------------+-----------
 public           | hello_35451   | hello          | PLPGSQL           |                |                | 
 public           | hello_35452   | hello          | PLPGSQL           | whom           | IN             | text
 public           | hello_35453   | hello          | PLPGSQL           | id             | IN             | integer
 public           | hello_35453   | hello          | PLPGSQL           | whom           | IN             | text
(4 rows)

The set session command maps the videodb catalog for the following anonymous block program.

SET SESSION "videodb.catalog_name" = 'videodb';

The following anonymous block lets you get rid of any ordinary or overloaded function and procedure:

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

DO $$
DECLARE
  /* 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 FOR
    SELECT routine_name
    ,      specific_name
    ,      routine_type
    FROM   information_schema.routines
    WHERE  specific_catalog = current_setting('videodb.catalog_name')
    AND    routine_schema = 'public';
 
  /* Declare a parameter cursor. */
  parameter_cursor CURSOR (cv_specific_name varchar) FOR
    SELECT args.data_type
    FROM   information_schema.parameters args
    WHERE  args.specific_schema = 'public'
    AND    args.specific_name = cv_specific_name;
 
BEGIN
  /* Open the cursor. */
  OPEN routine_cursor;
  <<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;
  END LOOP;
 
  /* Close the routine_cursor. */
  CLOSE routine_cursor;
END;
$$;

Now, you possess the magic to automate cleaning up your schema when you combine this with my earlier post on dynamically dropping tables, sequences, and triggers.

Written by maclochlainn

November 5th, 2019 at 12:11 am

Posted in Fedora,Linux,Postgres,PostgreSQL,sql,Stored Procedures

Tagged with , , ,

«
»