Cambiar de UTF8 a LATIN1 tu base postgres

Cambiar de UTF8 a LATIN1 tu base de datos postgres


POSTGRES SQL
psql -l

      Listado de base de datos
  Nombre   |  Dueño   | Codificación
-----------+----------+--------------
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
(3 filas)

psql postgres
update pg_database set encoding=8 where datname='postgres';
update pg_database set encoding=8 where datname='template0';
update pg_database set encoding=8 where datname='template1';
\q
psql -l

      Listado de base de datos
  Nombre   |  Dueño   | Codificación
-----------+----------+--------------
 postgres  | postgres | LATIN1
 template0 | postgres | LATIN1
 template1 | postgres | LATIN1
(3 filas)

Calcular el PROMEDIO AVG de un timestamp o date

Calcular el promedio de una fecha o timestamp en postgres



POSTGRESSQL

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + avg(extract(epoch from fechita)) * INTERVAL '1 second'
FROM fechas;


Donde fechita es el campo timestamp y fechas es la tabla o subconsulta

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

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"