jOOQ

edb-postgresplus-routines-1.sql

 WITH udf AS
  ( SELECT s.nspname ::text AS SCHEMA_NAME,
           n.nspname ::text AS package_name,
           p.proname ::text,
           CASE
               WHEN p.protype = '0' :: "char" THEN 'FUNCTION' ::text
               ELSE 'PROCEDURE' ::text
           END AS TYPE,
           edb_get_function_arguments ( p.oid) AS arguments,
           edb_get_function_result ( p.oid) AS return_type
   FROM pg_proc p
   LEFT JOIN
     ( SELECT pg_namespace.nspname,
              oid,
              pg_namespace.nspparent
      FROM pg_namespace
      WHERE pg_namespace.nspparent <> 0::oid
        AND pg_namespace.nspobjecttype = 0::oid) n ON (p.pronamespace = n.oid)
   JOIN pg_namespace s ON s.oid = n.nspparent
   WHERE s.nspname <> 'pg_catalog'
     AND s.nspname <> 'information_schema'
     AND s.nspname <> 'sys'
     AND s.nspname <> 'dbo'),
      overload_count AS
  ( SELECT SCHEMA_NAME,
           package_name,
           proname,
           TYPE,
           COUNT ( 1)
   FROM udf
   GROUP BY SCHEMA_NAME,
            package_name,
            proname,
            TYPE)
SELECT o.SCHEMA_NAME,
         o.package_name,
         o.proname,
         u.arguments,
         u.return_type,
         COUNT AS overloaded
FROM overload_count o
JOIN udf u ON ( u.SCHEMA_NAME = o.SCHEMA_NAME
               AND o.package_name = u.package_name
               AND o.proname = u.proname)
--WHERE COUNT > 1;

edb-postgresplus-routines-2.sql

SELECT
    SCHEMA,
    PACKAGE_NAME,
    PROCEDURE_NAME,
    TYPE,
    arguments,
    return_type,
    CASE
        WHEN is_overloaded = 1 THEN NULL
        ELSE overloaded
    END AS overloaded
FROM (
        SELECT
            n.nspname AS SCHEMA,
            NULL AS PACKAGE_NAME,
            p.proname AS PROCEDURE_NAME,
            CASE
                WHEN p.protype = '0' :: "char" THEN 'FUNCTION' ::text
                ELSE 'PROCEDURE' ::text
            END AS TYPE,
            row_number ()
            OVER (
                PARTITION BY
                    n.nspname,
                    p.proname,
                    p.protype ) AS OVERLOADED,
            count ( 1 )
            OVER (
                PARTITION BY
                    n.nspname,
                    p.proname,
                    p.protype )
            IS_OVERLOADED,
            edb_get_function_arguments ( p.oid ) AS arguments,
            edb_get_function_result ( p.oid ) AS return_type
        FROM
            pg_proc p
        LEFT JOIN (
            SELECT
                oid,
                *
            FROM
                pg_namespace
            WHERE
                nspparent = 0 )
        n ON ( p.pronamespace = n.oid )
    WHERE
        n.nspname NOT IN (
            'pg_catalog',
            'sys',
            'dbo',
            'information_schema' )
    UNION
    SELECT
        n.nspname AS SCHEMA,
        pkg.pkgname,
        p.proname AS PROCEDURE_NAME,
        CASE
            WHEN p.protype = '0' :: "char" THEN 'FUNCTION' ::text
            ELSE 'PROCEDURE' ::text
        END AS TYPE,
        row_number ()
        OVER (
            PARTITION BY
                n.nspname,
                pkg.pkgname,
                p.proname,
                p.protype ) AS OVERLOADED,
        count (1)
        OVER (
            PARTITION BY
                n.nspname,
                pkg.pkgname,
                p.proname,
                p.protype )
        IS_OVERLOADED,
        edb_get_function_arguments ( p.oid ) AS arguments,
        edb_get_function_result ( p.oid ) AS return_type
    FROM
        pg_proc p
    LEFT JOIN edb_package pkg ON ( p.pronamespace = pkg.oid )
    LEFT JOIN pg_namespace n ON ( pkg.pkgnamespace = n.oid )
WHERE
    n.nspname NOT IN ('pg_catalog', 'sys', 'dbo', 'information_schema' ) )
ORDER BY 1, 2, 3, 4;

edb-postgresplus-routines-3.sql

WITH routines AS (
  -- routines
  SELECT
    r.routine_schema AS schema_name,
    NULL AS package_name,
    r.routine_name,
    edb_get_function_arguments(p.oid) AS arguments,
    CASE
    WHEN EXISTS
    (SELECT 1 AS one
     FROM information_schema.parameters
     WHERE (information_schema.parameters.specific_schema = r.specific_schema
            AND information_schema.parameters.specific_name = r.specific_name
            AND upper(information_schema.parameters.parameter_mode) <> 'IN'))
      THEN 'void'
    ELSE r.data_type
    END AS data_type,
    r.type_udt_schema,
    r.type_udt_name,
    p.proisagg AS is_aggregate
  FROM information_schema.routines AS r
    JOIN pg_catalog.pg_namespace AS n ON r.specific_schema = n.nspname
    JOIN pg_catalog.pg_proc AS p ON (p.pronamespace = n.oid
                                     AND ((p.proname || '_') || cast(p.oid AS VARCHAR)) = r.specific_name)
  WHERE (r.routine_schema IN ('metrics', 'terradatum')
         AND NOT (p.proretset))
  UNION
  -- packages
  SELECT
    n.nspname AS schema_name,
    pkg.pkgname AS package_name,
    r.routine_name,
    edb_get_function_arguments(p.oid) AS arguments,
    CASE
    WHEN EXISTS
    (SELECT 1 AS one
     FROM information_schema.parameters
     WHERE (information_schema.parameters.specific_schema = r.specific_schema
            AND information_schema.parameters.specific_name = r.specific_name
            AND upper(information_schema.parameters.parameter_mode) <> 'IN'))
      THEN 'void'
    ELSE r.data_type
    END AS data_type,
    r.type_udt_schema,
    r.type_udt_name,
    p.proisagg AS is_aggregate
  FROM information_schema.routines AS r
    JOIN pg_catalog.edb_package AS pkg ON r.specific_schema = pkg.pkgname
    JOIN pg_catalog.pg_namespace AS n ON pkg.pkgnamespace = n.oid
    JOIN pg_catalog.pg_proc AS p ON (pkg.oid = p.pronamespace
                                     AND ((p.proname || '_') || cast(p.oid AS VARCHAR)) = r.specific_name)
  WHERE (n.nspname IN ('metrics', 'terradatum')
         AND NOT (p.proretset)))
SELECT
  schema_name,
  package_name,
  routine_name,
  data_type,
  CASE
  WHEN count(1)
       OVER (
         PARTITION BY
           schema_name,
           package_name,
           routine_name,
           data_type) = 1
    THEN NULL
  ELSE row_number()
  OVER (
    PARTITION BY
      schema_name,
      package_name,
      routine_name,
      data_type)
  END AS overloaded,
  type_udt_schema,
  type_udt_name,
  is_aggregate
FROM routines
ORDER BY schema_name ASC,
  package_name ASC,
  routine_name ASC

postgres-routines.sql

SELECT r1.routine_schema,
       r1.routine_name,
       r1.specific_name,
       CASE
           WHEN EXISTS
                  (SELECT 1 AS one
                   FROM information_schema.parameters
                   WHERE (information_schema.parameters.specific_schema = r1.specific_schema
                          AND information_schema.parameters.specific_name = r1.specific_name
                          AND upper(information_schema.parameters.parameter_mode) <> 'IN')) THEN 'void'
           ELSE r1.data_type
       END AS data_type,
       r1.character_maximum_length,
       r1.numeric_precision,
       r1.numeric_scale,
       r1.type_udt_schema,
       r1.type_udt_name,
       CASE
           WHEN EXISTS
                  (SELECT 1 AS one
                   FROM information_schema.routines AS r2
                   WHERE (r2.routine_schema IN ('metrics',
                                                    'terradatum')
                          AND r2.routine_schema = r1.routine_schema
                          AND r2.routine_name = r1.routine_name
                          AND r2.specific_name <> r1.specific_name)) THEN
                  (SELECT count(*)
                   FROM information_schema.routines AS r2
                   WHERE (r2.routine_schema IN ('metrics',
                                                    'terradatum')
                          AND r2.routine_schema = r1.routine_schema
                          AND r2.routine_name = r1.routine_name
                          AND r2.specific_name <= r1.specific_name))
       END AS overload,
       pg_catalog.pg_proc.proisagg
FROM information_schema.routines AS r1
JOIN pg_catalog.pg_namespace ON pg_catalog.pg_namespace.nspname = r1.specific_schema
JOIN pg_catalog.pg_proc ON (pg_catalog.pg_proc.pronamespace = pg_catalog.pg_namespace.oid
                                AND ((pg_catalog.pg_proc.proname || '_') || cast(pg_catalog.pg_proc.oid AS varchar)) = r1.specific_name)
WHERE (r1.routine_schema IN ('metrics',
                                 'terradatum')
       AND not(pg_catalog.pg_proc.proretset))
ORDER BY r1.routine_schema ASC,
         r1.routine_name ASC

postgresplus-routines.sql

SELECT n1.nspname, 
       p.pkgname, 
       r1.routine_name,
       CASE
           WHEN EXISTS
                  (SELECT 1 AS one
                   FROM information_schema.parameters
                   WHERE (information_schema.parameters.specific_schema = r1.specific_schema
                          AND information_schema.parameters.specific_name = r1.specific_name
                          AND upper(information_schema.parameters.parameter_mode) <> 'IN')) THEN 'void'
           ELSE r1.data_type
       END AS data_type,
       r1.type_udt_schema,
       r1.type_udt_name,
       CASE
           WHEN EXISTS
                  (SELECT 1 AS one
			FROM pg_catalog.edb_package AS p
			JOIN pg_catalog.pg_namespace AS n1 ON p.pkgnamespace = n1.oid
			JOIN information_schema.routines AS r2 ON p.pkgname = r2.routine_name
                   WHERE (n1.nspname IN ('metrics',
                                             'terradatum')
                          AND r2.routine_schema = r1.routine_schema
                          AND r2.routine_name = r1.routine_name
                          AND r2.specific_name <> r1.specific_name)) THEN
                  (SELECT count(*)
			FROM pg_catalog.edb_package AS p
			JOIN pg_catalog.pg_namespace AS n1 ON p.pkgnamespace = n1.oid
			JOIN information_schema.routines AS r2 ON p.pkgname = r2.routine_name
                   WHERE (n1.nspname IN ('metrics',
                                             'terradatum')
                          AND r2.routine_schema = r1.routine_schema
                          AND r2.routine_name = r1.routine_name
                          AND r2.specific_name <= r1.specific_name))
       END AS overload,
       pp.proisagg
FROM pg_catalog.edb_package AS p
JOIN pg_catalog.pg_namespace AS n1 ON p.pkgnamespace = n1.oid
JOIN information_schema.routines AS r1 ON p.pkgname = r1.specific_schema
JOIN pg_catalog.pg_namespace AS n2 ON n2.nspname = r1.specific_schema
JOIN pg_catalog.pg_proc AS pp ON (pp.pronamespace = n2.oid
                                AND ((pp.proname || '_') || cast(pp.oid AS varchar)) = r1.specific_name)
WHERE (n1.nspname IN ('metrics',
                         'terradatum')
       AND not(pp.proretset))
ORDER BY n1.nspname ASC,
	 p.pkgname ASC,
         r1.routine_name ASC

PostgresPlusTableDefinition.java

package com.terradatum.jooq.util.postgresplus;

import org.jooq.util.*;
import org.jooq.util.postgres.PostgresDataType;
import org.jooq.util.postgres.PostgresTableDefinition;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * Created by rbellamy on 1/4/16.
 */
public class PostgresPlusTableDefinition extends PostgresTableDefinition {
    public PostgresPlusTableDefinition(SchemaDefinition schema, String name, String comment) {
        super(schema, name, comment);
    }

    @Override
    public List<ColumnDefinition> getElements0() throws SQLException {
        
        // include the 'hidden' OID column from system tables
        if (getSchema().getName().equals("pg_catalog")) {

            List<ColumnDefinition> columnDefinitions = new ArrayList<>();

            Optional<ColumnDefinition> oidColumnDefinition = columnDefinitions
                    .stream()
                    .filter(col -> col.getName().equals("oid"))
                    .findAny();

            if (!oidColumnDefinition.isPresent()) {

                int position = 0;

                DataTypeDefinition dataTypeDefinition = new DefaultDataTypeDefinition(
                        getDatabase(),
                        getSchema(),
                        PostgresDataType.OID.getTypeName()
                );

                ColumnDefinition newOidColumnDefinition = new DefaultColumnDefinition(
                        this,
                        "oid",
                        position,
                        dataTypeDefinition,
                        true,
                        "System table object identifier"
                );
                columnDefinitions.add(newOidColumnDefinition);

                for (ColumnDefinition oldColumnDefinition : super.getElements0()) {
                    position++;

                    ColumnDefinition newColumnDefinition = new DefaultColumnDefinition(
                            getDatabase().getTable(getSchema(), getName()),
                            oldColumnDefinition.getName(),
                            position,
                            oldColumnDefinition.getType(),
                            oldColumnDefinition.isIdentity(),
                            oldColumnDefinition.getComment()
                    );
                    columnDefinitions.add(newColumnDefinition);
                }
            }
            return columnDefinitions;
        } else {
            return super.getElements0();
        }
    }
}