Sql := 'DROP ' ||row.routine_type || ' IF EXISTS ' ||row.routine_name ||list /* Execute the DDL statement. */ĬLOSE parameter_cursor /* Concatenate together a DDL to drop the table with prejudice. List := CONCAT (list, ')' ) /* Close the parameter cursor. List := CONCAT (list ,arg ) END IF END LOOP /* Close the parameter list. */ĮXIT parameter_loop WHEN NOT FOUND /* Add parameter and delimit more than one parameter with a comma. OPEN parameter_cursor (row.specific_name:: varchar ) >įETCH parameter_cursor INTO arg /* Exit the parameter loop. List := '(' /* Open the parameter cursor. */ĮXIT row_loop WHEN NOT FOUND /* Initialize parameter list. */įETCH routine_cursor INTO row /* Exit when no more records are found. WHERE args.specific_schema = 'public' AND args.specific_name = cv_specific_name BEGIN /* Open the cursor.
Parameter_cursor CURSOR (cv_specific_name varchar ) FOR WHERE specific_catalog = current_setting ( 'videodb.catalog _name' ) AND routine_schema = 'public' /* Declare a parameter cursor. List VARCHAR /* Declare a routine cursor. Sql VARCHAR /* Declare variables to manage cursor return values. The set session command maps the videodb catalog for the following anonymous block program.ĭECLARE /* Declare an indefinite length string for SQL statement. Public | hello_35453 | hello | PLPGSQL | whom | IN | text Public | hello_35453 | hello | PLPGSQL | id | IN | integer Public | hello_35452 | hello | PLPGSQL | whom | IN | text Public | hello_35451 | hello | PLPGSQL | | | Procedure_schema | specific_name | procedure_name | external_language | parameter_name | parameter_mode | data_type WHERE proc.routine_schema NOT IN ('pg_catalog', 'information_schema')ĪND proc.routine_type IN ('FUNCTION','PROCEDURE') WHERE proc.routine_schema NOT IN ( 'pg_catalog', 'information_schema' ) AND proc.routine_type IN ( 'FUNCTION', 'PROCEDURE' ) ORDER BY procedure_schema
ON proc.specific_schema = args.specific_schemaĪND proc.specific_name = args.specific_name SELECT proc.specific_schema AS procedure_schemaįROM information_schema.routines proc left join information_schema.parameters args You can test the overloaded hello function, like so from the videodb schema: SELECT CONCAT(' Hello ',whom,'!') INTO output SELECT CONCAT('Hello ',whom,'!') INTO output Output VARCHAR BEGIN SELECT CONCAT ( ' Hello ' ,whom, '!' ) INTO output $$ LANGUAGE plpgsql CREATE FUNCTION hello (id int, whom text ) RETURNS text AS Output VARCHAR BEGIN SELECT CONCAT ( 'Hello ' ,whom, '!' ) INTO output $$ LANGUAGE plpgsql CREATE FUNCTION hello (whom text ) RETURNS text AS
Output VARCHAR BEGIN SELECT 'Hello World!' INTO output CREATE FUNCTION hello ( ) RETURNS text AS
0 kommentar(er)
