PostgreSQL Functions

Functions for retrieving return types and input parameters from PostgreSQL procedures.

wizards.getreturntypes

Retrieves return types of functions within a schema, breaking down TABLE return types into individual columns.

Function Definition

CREATE OR REPLACE FUNCTION wizards.getreturntypes(input_schemaname text)
RETURNS TABLE(
    schemaname_ text,
    functionname_ text,
    returncolumnname_ text,
    returncolumntype_ text
) AS $$
DECLARE
    funcname text;
    returntype text;
    column_definition text;
    column_name text;
    column_type text;
BEGIN
    FOR funcname, returntype IN
        SELECT p.proname, pg_catalog.pg_get_function_result(p.oid)
        FROM pg_catalog.pg_proc p
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
        WHERE n.nspname = input_schemaname
    LOOP
        IF returntype LIKE 'TABLE%' THEN
            returntype := substr(returntype, 7, length(returntype) - 7);
            returntype := substr(returntype, 1, length(returntype) - 1);

            FOR column_definition IN
                SELECT unnest(string_to_array(returntype, ','))
            LOOP
                column_definition := trim(column_definition);
                column_name := trim(split_part(column_definition, ' ', 1));
                column_type := trim(split_part(column_definition, ' ', 2));

                schemaname_ := input_schemaname;
                functionname_ := funcname;
                returncolumnname_ := column_name;
                returncolumntype_ := column_type;
                RETURN NEXT;
            END LOOP;
        ELSE
            schemaname_ := input_schemaname;
            functionname_ := funcname;
            returncolumnname_ := NULL;
            returncolumntype_ := returntype;
            RETURN NEXT;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Process

1. Extract function names and return types using pg_get_function_result
2. Check if return type is TABLE
3. Remove TABLE( prefix and trailing )
4. Split column definitions using string_to_array and unnest
5. Parse each column into name and type

wizards.getinputparams

Retrieves input parameters of functions within a schema.

Function Definition

CREATE OR REPLACE FUNCTION wizards.getinputparams(input_schemaname text)
RETURNS TABLE(
    schemaname_ text,
    functionname_ text,
    paramname_ text,
    paramtype_ text
) AS $$
DECLARE
    funcname text;
    paramstring text;
    param_definition text;
    param_name text;
    param_type text;
BEGIN
    FOR funcname, paramstring IN
        SELECT p.proname, pg_catalog.pg_get_function_arguments(p.oid)
        FROM pg_catalog.pg_proc p
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
        WHERE n.nspname = input_schemaname
    LOOP
        FOR param_definition IN
            SELECT unnest(string_to_array(paramstring, ','))
        LOOP
            param_definition := trim(param_definition);

            IF position(' ' IN param_definition) > 0 THEN
                param_name := trim(split_part(param_definition, ' ', 1));
                param_type := trim(split_part(param_definition, ' ', 2));
            ELSE
                param_name := NULL;
                param_type := trim(param_definition);
            END IF;

            schemaname_ := input_schemaname;
            functionname_ := funcname;
            paramname_ := param_name;
            paramtype_ := param_type;
            RETURN NEXT;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Process

1. Extract function names and parameters using pg_get_function_arguments
2. Split parameter string into individual parameters
3. Parse each parameter into name and type
4. Handle unnamed parameters (type only)

Notes on String Manipulation

The functions use substr instead of regular expressions to handle TABLE return types. This avoids issues with unbalanced parentheses that can occur with regex patterns like ^TABLE\((.*)\)$.