Pivotal Knowledge Base

Follow

Dropping a user-defined function when the function argument types can not be remembered

Environment 

Pivotal Greenplum: All versions

Purpose
Unlike other relational databases, dropping of user-defined functions in Postgresql/Greenplum needs the argument type of the function for the DROP statement. Sometimes, we might not remember the argument type of a function. 

Solution

Take for example the following case.

We create a simple function:

testDB=# CREATE OR REPLACE FUNCTION sum_int(i integer, j integer) RETURNS
testDB-# integer AS $$
testDB$# BEGIN
testDB$# RETURN i + j;
testDB$# END;
testDB$# $$ LANGUAGE plpgsql;

We can have 2 simple ways to get the argument type of the function:

1. Use the command "\df" to check for the argument

testDB=# \df sum_int
List of functions
-[ RECORD 1 ]-------+---------------------
Schema | public
Name | sum_int
Result data type | integer
Argument data types | i integer, j integer
Type | normal

2. Use following queries

a) Get the argument type ID of the function

testDB=# select * from pg_proc where proname='sum_int';
-[ RECORD 1 ]--+------------------------------
proname | sum_int
pronamespace | 2200
proowner | 10
prolang | 10886
proisagg | f
prosecdef | f
proisstrict | f
proretset | f
provolatile | v
pronargs | 2
prorettype | 23
proiswin | f
proargtypes | 23 23
proallargtypes |
proargmodes |
proargnames | {i,j}

From the output above, we can see the type ID of both the two arguments is 23

b) We can then check pg_type table to know the type name

testDB=# select typname from pg_type where oid = 23;
-[ RECORD 1 ]-+---------
typname | int4

c) After that, we can directly use the type name "int4" to drop the function

testDB=# drop function sum_int(int4,int4);
DROP FUNCTION

Comments

Powered by Zendesk