Monday, 04 November 2019 07:11

Postgres Overloaded Routines

Written by http://blog.mclaughlinsoftware.com/2019/11/05/postrgres-overloaded-routines/
Rate this item
(0 votes)

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:

CREATEFUNCTION hello()RETURNStextAS$$DECLARE output VARCHAR;BEGINSELECT'Hello World!'INTO output; RETURN output;END$$ LANGUAGE plpgsql;CREATEFUNCTION hello(whom text)RETURNStextAS$$DECLARE output VARCHAR;BEGINSELECTCONCAT('Hello ',whom,'!')INTO output; RETURN output;END$$ LANGUAGE plpgsql;CREATEFUNCTION hello(id int, whom text)RETURNStextAS$$DECLARE output VARCHAR;BEGINSELECTCONCAT('[',id,'] Hello ',whom,'!')INTO output; RETURN output;END$$ LANGUAGE plpgsql;

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!(1ROW) videodb=>SELECT hello('Captain Marvel'); hello ----------------------- Hello Captain Marvel!(1ROW) videodb=>SELECT hello(1,'Captain America'); hello ----------------------------[1] Hello Captain America!(1ROW)

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_typeFROM information_schema.routines proc left join information_schema.parameters argsON proc.specific_schema = args.specific_schemaAND proc.specific_name = args.specific_nameWHERE proc.routine_schema NOTIN('pg_catalog','information_schema')AND proc.routine_type IN('FUNCTION','PROCEDURE')ORDERBY procedure_schema, specific_name, procedure_name, args.ordinal_position;

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)

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.

SETSESSION"videodb.catalog_name"='videodb';

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

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

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576
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 WHENNOT 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 WHENNOT FOUND;/* Add parameter and delimit more than one parameter with a comma. */IFLENGTH(list)>1THEN list :=CONCAT(list,',',arg);ELSE list :=CONCAT(list,arg);ENDIF;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;$$;

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.

Read 29 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.