Listar Funciones de una base de datos PostgreSQL 8.1

Algunas veces es necesario recuperar el codigo de una funcion de la base de datos, o un grupo de funciones bajo un patron, por ejemplo quiero todas las funciones que empiecen con "act", en otros casos es necesario listar las declaraciones de las funciones para armar un catalogo o algo asi.



POSTGRES SQL

create or replace function ldj_listar_funciones(_nombre_funcion text, _codigo boolean) returns setof record as $$
-------------------------------
-- Autor: Luis A. Jordan P.
-- email: jordanluis@yahoo.com
-------------------------------
declare
  _bucle   record;
begin
  for _bucle in
  SELECT (case when _codigo then 'CREATE OR REPLACE ' else '' end || 'FUNCTION '|| p.proname ||'(' ||
      CASE WHEN proallargtypes IS NOT NULL THEN
        pg_catalog.array_to_string(
          ARRAY(
            SELECT
              CASE
                WHEN p.proargmodes[s.i] = 'i' THEN ''
                WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
                WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
              END ||
              CASE
                WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
                ELSE p.proargnames[s.i] || ' '
              END ||
              pg_catalog.format_type(p.proallargtypes[s.i], NULL)
            FROM pg_catalog.generate_series(
              1, pg_catalog.array_upper(p.proallargtypes, 1)
            ) AS s(i)
          ), ', '
        )
      ELSE
        pg_catalog.array_to_string(
          ARRAY(
            SELECT
              CASE
                WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''
                ELSE p.proargnames[s.i+1] || ' '
                END ||
              pg_catalog.format_type(p.proargtypes[s.i], NULL)
            FROM
              pg_catalog.generate_series(
                0, pg_catalog.array_upper(p.proargtypes, 1)
              ) AS s(i)
          ), ', '
        )
      END ||
      CASE WHEN p.proretset THEN ') returns setof ' ELSE ') returns ' END ||
      pg_catalog.format_type(p.prorettype, NULL)  || case when _codigo then ' as \$\$ ' || p.prosrc
      || '\$\$ language '||lanname else '' end ||';')::text as funcion
    FROM pg_catalog.pg_proc p
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
         LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
         JOIN pg_catalog.pg_roles r ON r.oid = p.proowner
    WHERE p.proname ilike _nombre_funcion
    AND NOT p.proisagg
    AND pg_catalog.pg_function_is_visible(p.oid)
  loop
    return next _bucle;
  end loop;
  return;
end;
$$ language plpgsql;

SELECT * FROM ldj_listar_funciones('act%', true) as (funcion text);

SELECT * FROM ldj_listar_funciones('act%', false) as (funcion text); --catalogo

Funciones para manejo de cadenas

Manejo de cadenas, la funcion repeat permite repetir un patron varias veces, la funcion lpad, rellena un numero de caracteres al lado izquierdo de una cadena, la funcion rpad, rellena un numero de caracteres al lado derecho de una cadena

Por ejemplo:

repeat("x", 5) --> "xxxxx"
lpad("hola", 6, "x") --> "xxhola"
lpad("hola", 4, "x") --> "hola"
rpad("hola", 6, "x") --> "holaxx"
rpad("1.5", 4, "0") --> "1.50"



JAVA

  public String repeat (String pattern, int tamano){
    if(pattern==null)return null;
    if(tamano<1) return "";
    String cadena="";
    for(int i=0;i<tamano;i++){
      cadena+=pattern;
    }
    return cadena;
  }
  public String lpad (String cadena, int tamano, String pattern){
    if(cadena==null)return null;
    if(pattern==null)return null;    
    return repeat(pattern,tamano-cadena.length())+cadena;
  }
  public String rpad (String cadena, int tamano, String pattern){
    if(cadena==null)return null;
    if(pattern==null)return null;    
    return cadena+repeat(pattern,tamano-cadena.length());
  }

Devuelve la fecha de hoy

Obtener la fecha de hoy en formato DD/MM/YYYY


JAVA

  public String hoy(){
      Date dFecha_actual = new Date();
      String sDia = Integer.toString(dFecha_actual.getDate());
      String sMes = Integer.toString(dFecha_actual.getMonth() + 1);
      String sAnio = Integer.toString(dFecha_actual.getYear() + 1900);
      if(sDia.length() == 1) sDia = "0" + sDia;
      if(sMes.length() == 1) sMes = "0" + sMes;
      String sFecha = sDia + "/" + sMes + "/" + sAnio;
      return sFecha;
  }

mes a literal

Convierte un mes a literal es decir en palabras 1 -->ENERO


JAVA
  public String mes2literal(int m){
    String sMes="";
    switch (m){
      case 1:
      sMes="ENERO";
      break;
      case 2:
      sMes="FEBRERO";
      break;
      case 3:
      sMes="MARZO";
      break;
      case 4:
      sMes="ABRIL";
      break;
      case 5:
      sMes="MAYO";
      break;
      case 6:
      sMes="JUNIO";
      break;
      case 7:
      sMes="JULIO";
      break;
      case 8:
      sMes="AGOSTO";
      break;
      case 9:
      sMes="SEPTIEMBRE";
      break;
      case 10:
      sMes="OCTUBRE";
      break;
      case 11:
      sMes="NOVIEMBRE";
      break;
      case 12:
      sMes="DICIEMBRE";
      break;
    }
    return sMes;
  }

numero a literal

Muchas veces se necesita presentar un numero en forma literal es decir en palabras, a continuacion se presenta una funcion que realiza la conversion de un numero entero a palabras 1000 --> "un mil"

POSTGRES SQL
create or replace function numero2literal(_num text) returns text as $$
------------------------
-- Autor: Luis Jordan P.
------------------------

declare
  _literal text;
  _numero text;
begin
  
  _numero:=ltrim(_num,'0');
  _numero:=replace(_numero,',','');
  if length(_numero)=1 then
    SELECT CASE
      WHEN _numero=0 THEN 'cero'
      WHEN _numero=1 THEN 'un'
      WHEN _numero=2 THEN 'dos'
      WHEN _numero=3 THEN 'tres'
      WHEN _numero=4 THEN 'cuatro'
      WHEN _numero=5 THEN 'cinco'
      WHEN _numero=6 THEN 'seis'
      WHEN _numero=7 THEN 'siete'
      WHEN _numero=8 THEN 'ocho'
      WHEN _numero=9 THEN 'nueve'
    ELSE ''
    END INTO _literal;
  end if;
  if length(_numero)=2 then
    SELECT CASE
      WHEN substring(_numero,1,1)=1 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'diez'
        WHEN substring(_numero,2,1)=1 THEN 'once'
        WHEN substring(_numero,2,1)=2 THEN 'doce'
        WHEN substring(_numero,2,1)=3 THEN 'trece'
        WHEN substring(_numero,2,1)=4 THEN 'catorce'
        WHEN substring(_numero,2,1)=5 THEN 'quince'
        WHEN substring(_numero,2,1) between 6 and 9 THEN 'diez y ' || numero2literal(substring(_numero,2,1))
      END  
      WHEN substring(_numero,1,1)=2 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'veinte'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'veinte y ' || numero2literal(substring(_numero,2,1))
      END
      WHEN substring(_numero,1,1)=3 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'treinta'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'treinta y ' || numero2literal(substring(_numero,2,1))
      END
      WHEN substring(_numero,1,1)=4 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'cuarenta'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'cuarenta y ' || numero2literal(substring(_numero,2,1))
      END
      WHEN substring(_numero,1,1)=5 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'cincuenta'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'cincuenta y ' || numero2literal(substring(_numero,2,1))
      END
      WHEN substring(_numero,1,1)=6 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'sesenta'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'sesenta y ' || numero2literal(substring(_numero,2,1))
      END
      WHEN substring(_numero,1,1)=7 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'setenta'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'setenta y ' || numero2literal(substring(_numero,2,1))
      END
      WHEN substring(_numero,1,1)=8 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'ochenta'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'ochenta y ' || numero2literal(substring(_numero,2,1))
      END
      WHEN substring(_numero,1,1)=9 THEN CASE
        WHEN substring(_numero,2,1)=0 THEN 'noventa'
        WHEN substring(_numero,2,1) between 1 and 9 THEN 'noventa y ' || numero2literal(substring(_numero,2,1))
      END
    ELSE ''
    END INTO _literal;
  end if;
  if length(_numero)=3 then
    SELECT CASE
      WHEN substring(_numero,1,1)=1 THEN CASE
        WHEN substring(_numero,2,2)='00' THEN 'cien'
        WHEN substring(_numero,2,2)<>'00' THEN 'ciento ' || numero2literal(substring(_numero,2,2))
      END  
      WHEN substring(_numero,1,1) between 2 and 8  THEN CASE
        WHEN substring(_numero,2,2)='00' THEN  numero2literal(substring(_numero,1,1)) || 'cientos'
        WHEN substring(_numero,2,2)<>'00' THEN numero2literal(substring(_numero,1,1)) || 'cientos ' || numero2literal(substring(_numero,2,2))
      END
      WHEN substring(_numero,1,1) = 9  THEN CASE
        WHEN substring(_numero,2,2)='00' THEN  'novecientos'
        WHEN substring(_numero,2,2)<>'00' THEN 'novecientos ' || numero2literal(substring(_numero,2,2))
      END
    ELSE ''
    END INTO _literal;
  end if;
  if length(_numero) between 4 and 6 then
    SELECT numero2literal(substring(_numero,1,length(_numero)-3)) || ' mil' ||
    CASE WHEN substring(_numero, length(_numero)-2,3)='000' THEN ''
    ELSE ' ' || numero2literal(substring(_numero, length(_numero)-2,3))
    END
    INTO _literal;
  end if;
  if length(_numero) between 7 and 12 then  
    SELECT numero2literal(substring(_numero,1,length(_numero)-6)) ||
    CASE WHEN substring(_numero,1,length(_numero)-6) ='1' THEN ' millon'
    ELSE ' millones'
    END
    ||
    CASE WHEN substring(_numero, length(_numero)-5,6)='000000' THEN ''
    ELSE ' ' || numero2literal(substring(_numero, length(_numero)-5,6))
    END
    INTO _literal;
  end if;

  return _literal;
end;
$$ language plpgsql;


select numero2literal('999999999999');

Resultado:
"novecientos noventa y nueve mil novecientos noventa y nueve millones novecientos noventa y nueve mil novecientos noventa y nueve"

fecha a literal (texto)

Muchas veces se necesita presentar una fecha en forma literal es decir en palabras, a veces con o sin el dia, a continuacion se presenta una funcion que realiza la conversion de una fecha YYYY-MM-DD a palabras nombre_dia dia de mes de año





POSTGRES SQL

CREATE OR REPLACE FUNCTION _fecha_literal(_fecha date, _dia integer) RETURNS text AS $$
-- _dia =0/1   sin/con el dia de la semana
------------------------
-- Autor: Luis Jordan P.
------------------------

declare
  _salida text;
begin
SELECT
  CASE WHEN _dia=1 THEN
    CASE WHEN rtrim(to_char(_fecha, 'day'))='sunday' THEN 'domingo '
           WHEN rtrim(to_char(_fecha, 'day'))='monday' THEN 'lunes '
           WHEN rtrim(to_char(_fecha, 'day'))='tuesday' THEN 'martes '
           WHEN rtrim(to_char(_fecha, 'day'))='wednesday' THEN 'miercoles '
           WHEN rtrim(to_char(_fecha, 'day'))='thursday' THEN 'jueves '
           WHEN rtrim(to_char(_fecha, 'day'))='friday' THEN 'viernes '
           WHEN rtrim(to_char(_fecha, 'day'))='saturday' THEN 'sabado '
      ELSE ''
      END
  ELSE ''
  END
  ||
  extract(day from _fecha) || ' de ' ||
    CASE WHEN extract(month from _fecha) =01 THEN 'enero'
         WHEN extract(month from _fecha) =02 THEN 'febrero'
         WHEN extract(month from _fecha) =03 THEN 'marzo'
         WHEN extract(month from _fecha) =04 THEN 'abril'
         WHEN extract(month from _fecha) =05 THEN 'mayo'
         WHEN extract(month from _fecha) =06 THEN 'junio'
         WHEN extract(month from _fecha) =07 THEN 'julio'
         WHEN extract(month from _fecha) =08 THEN 'agosto'
         WHEN extract(month from _fecha) =09 THEN 'septiembre'
         WHEN extract(month from _fecha) =10 THEN 'octubre'
         WHEN extract(month from _fecha) =11 THEN 'noviembre'
         WHEN extract(month from _fecha) =12 THEN 'diciembre'
     END
      || ' de ' || extract(year from _fecha)  
        
  INTO _salida;
  return _salida;
end; $$ LANGUAGE plpgsql;

SELECT _fecha_literal('2007-12-10'::date, 1);

Resultado:
lunes 10 de diciembre de 2007

Listar las hojas de un arbol a partir de un nodo

Si tengo una tabla arbol y deseo listar solo los nodos hojas a partir de un nodo raiz, puedo utilizar la funcion que presento a continuacion, si se selecciona un nodo hoja, se mostrara a si mismo, pero no se mostraran nodos padres

POSTGRES SQL

CREATE OR REPLACE FUNCTION listar_hijos(_tabla text, _id_campo text
, _id_padre text, _id integer, _where text) RETURNS text AS $$
------------------------
-- Autor: Luis Jordan P.
------------------------
declare
  _sql text;
  _bucle record;
  _i integer;
  _ids text;
begin
  _sql := '
  SELECT a.' || _id_campo || ' as id  FROM '|| _tabla ||' a, '|| _tabla ||' b
  WHERE a.'|| _id_padre ||' = b.'|| _id_campo ||'
  AND b.' || _id_campo || ' = ' || _id
  || _where;
  raise notice 'sql:%',_sql;
  _i:=0;
  _ids := '';
  for _bucle in
    execute _sql
  loop
    _i:=_i+1;
    if (_id <> _bucle.id) then
      _ids := _ids || (SELECT * FROM listar_hijos(_tabla, _id_campo
      , _id_padre, _bucle.id, _where));
    end if;
  end loop;
  if (_i = 0) then  -- comentar el if si se desea que se incluyan los padres
    _ids := _ids || ',' || _id;
  end if;
  return _ids;
end;
$$ LANGUAGE 'plpgsql' VOLATILE;

--ejemplo con WHERE:
--SELECT listar_hijos('tabla', 'id_campo', 'id_campo_padre', 1, ' AND a.campo=''Luis'' ');


SELECT listar_hijos('tabla', 'id_campo', 'id_campo_padre', 1, ' ');
Resultado:
,4,8,9,6,7