principales - postgresql sintaxis

¿FUNCIÓN DE CAÍDA sin conocer el número/tipo de parámetros?

Question

''CREATE OR REPLACE FUNCTION somefunction'' todas mis funciones en un archivo de texto con ''CREATE OR REPLACE FUNCTION somefunction'' . Entonces, si agrego o cambio alguna función, simplemente le envío el archivo a psql.

Ahora bien, si agrego o elimino parámetros a una función existente, se crea una sobrecarga con el mismo nombre y para eliminar el original. Necesito escribir en todos los tipos de parámetros en el orden exacto, lo que es bastante tedioso.

¿Hay algún tipo de comodín que pueda usar para DROP todas las funciones con un nombre dado, así que puedo agregar líneas DROP FUNCTION al principio de mi archivo?

Answer 1

Consulta básica

Esta consulta crea todas las sentencias DDL necesarias (simplificadas con cast para regprocedure ):

SELECT ''DROP FUNCTION '' || oid::regprocedure
FROM   pg_proc
WHERE  proname = ''my_function_name''  -- name without schema-qualification
AND    pg_function_is_visible(oid);  -- restrict to current search_path ..
                                     -- .. you may or may not want this

Salida:

DROP FUNCTION my_function_name(string text, form text, maxlen integer);
DROP FUNCTION my_function_name(string text, form text);
DROP FUNCTION my_function_name(string text);

Ejecute los comandos (después de una verificación de plausibilidad).

El nombre de la función pg_proc.proname entre mayúsculas y minúsculas y no pg_proc.proname comillas dobles cuando se pasa como parámetro de text para que coincida con pg_proc.proname .

La regprocedure tipo de identificador de objeto ( oid::regprocedure ) hace que todos los identificadores sean seguros contra la inyección de SQL (a través de identificadores mal formados). Al convertir a text , el nombre de la función se cita doblemente y se califica por esquema de acuerdo con la search_path actual automáticamente cuando sea necesario.

pg_function_is_visible(oid) restringe la selección a funciones en el search_path actual. Puede o no querer eso. Con la condición pg_function_is_visible(oid) en su lugar, la función está garantizada para ser visible.

Si tiene múltiples funciones del mismo nombre en múltiples esquemas, o funciones sobrecargadas con varios argumentos de funciones, todas ellas se enumerarán por separado. Es posible que desee restringir a los esquemas específicos o los parámetros de funciones específicas después de todo.

Relacionado:

• ¿Cuándo / cómo se vinculan las funciones de expresión de valor por defecto con respecto a search_path?

Función

Puede construir una función plpgsql alrededor de esto para ejecutar las instrucciones inmediatamente con EXECUTE . Para Postgres 9.1 o posterior: ¡Cuidado! ¡Deja caer tus funciones!

CREATE OR REPLACE FUNCTION f_delfunc(_name text, OUT func_dropped int) AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT count(*)::int
        , ''DROP FUNCTION '' || string_agg(oid::regprocedure::text, ''; DROP FUNCTION '')
   FROM   pg_proc
   WHERE  proname = _name
   AND    pg_function_is_visible(oid)
   INTO   func_dropped, _sql;  -- only returned if trailing DROPs succeed

   IF func_dropped > 0 THEN    -- only if function(s) found
     EXECUTE _sql;
   END IF;
END
$func$ LANGUAGE plpgsql;

Llamada:

SELECT * FROM f_delfunc(''my_function_name'');

O solo:

SELECT f_delfunc(''my_function_name'');

De esta forma, no obtiene el nombre de la columna func_dropped para la columna de resultados. Puede que no te importe

La función devuelve el número de funciones encontradas y descartadas (sin excepciones) - 0 si no se encontró ninguna.

Supone una search_path pg_catalog (predeterminada) donde pg_catalog no se ha movido.
Más en estas respuestas relacionadas:

• ¿Cómo influye search_path en la resolución del identificador y el "esquema actual"?
• Truncar todas las tablas en una base de datos de Postgres
• PostgreSQL parametrizado por orden / límite en la función de tabla

Para las versiones de Postgres anteriores a 9.1 o versiones anteriores de la función que usan regproc y pg_get_function_identity_arguments(oid) verifique el historial de edición de esta respuesta.

Answer 2

Aquí está la consulta que construí sobre la solución @ Сухой27 que genera declaraciones SQL para eliminar todas las funciones almacenadas en un esquema:

WITH f AS (SELECT specific_schema || ''.'' || ROUTINE_NAME AS func_name 
        FROM information_schema.routines
        WHERE routine_type=''FUNCTION'' AND specific_schema=''a3i'')
SELECT
    format(''DROP FUNCTION %s(%s);'',
      p.oid::regproc, pg_get_function_identity_arguments(p.oid))
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
    p.oid::regproc::text IN (SELECT func_name FROM f);

Answer 3

Mejora de la answer original para tener en cuenta el schema , es decir. schema.my_function_name ,

select
    format(''DROP FUNCTION %s(%s);'',
      p.oid::regproc, pg_get_function_identity_arguments(p.oid))
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
    p.oid::regproc::text = ''schema.my_function_name'';

Answer 4

Tendrías que escribir una función que tomara el nombre de la función, y buscar cada sobrecarga con sus tipos de parámetros de information_schema , luego construir y ejecutar un DROP para cada uno.

EDITAR: Esto resultó ser mucho más difícil de lo que pensaba. Parece que information_schema no mantiene la información de parámetros necesaria en su catálogo de routines . Por lo tanto, debe usar tablas suplementarias de PostgreSQL pg_proc y pg_type :

CREATE OR REPLACE FUNCTION udf_dropfunction(functionname text)
  RETURNS text AS
$BODY$
DECLARE
    funcrow RECORD;
    numfunctions smallint := 0;
    numparameters int;
    i int;
    paramtext text;
BEGIN
FOR funcrow IN SELECT proargtypes FROM pg_proc WHERE proname = functionname LOOP

    --for some reason array_upper is off by one for the oidvector type, hence the +1
    numparameters = array_upper(funcrow.proargtypes, 1) + 1;

    i = 0;
    paramtext = '''';

    LOOP
        IF i < numparameters THEN
            IF i > 0 THEN
                paramtext = paramtext || '', '';
            END IF;
            paramtext = paramtext || (SELECT typname FROM pg_type WHERE oid = funcrow.proargtypes[i]);
            i = i + 1;
        ELSE
            EXIT;
        END IF;
    END LOOP;

    EXECUTE ''DROP FUNCTION '' || functionname || ''('' || paramtext || '');'';
    numfunctions = numfunctions + 1;

END LOOP;

RETURN ''Dropped '' || numfunctions || '' functions'';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Lo probé con éxito en una función sobrecargada. Fue lanzado bastante rápido, pero funciona bien como una función de utilidad. Recomendaría probar más antes de usarlo en la práctica, en caso de que pasara por alto algo.

Answer 5

Versión ligeramente mejorada de la respuesta de Erwin. Además, admite siguientes

• ''me gusta'' en lugar del nombre exacto de la función coincide
• puede ejecutarse en ''modo seco'' y ''rastrear'' el SQL para eliminar las funciones

Código para copiar / pegar:

/**
 * Removes all functions matching given function name mask
 *
 * @param p_name_mask   Mask in SQL ''like'' syntax
 * @param p_opts        Combination of comma|space separated options:
 *                        trace - output SQL to be executed as ''NOTICE''
 *                        dryrun - do not execute generated SQL
 * @returns             Generated SQL ''drop functions'' string
 */
CREATE OR REPLACE FUNCTION mypg_drop_functions(IN p_name_mask text,
                                               IN p_opts text = '''')
    RETURNS text LANGUAGE plpgsql AS $$
DECLARE
    v_trace boolean;
    v_dryrun boolean;
    v_opts text[];
    v_sql text;
BEGIN
    if p_opts is null then
        v_trace = false;
        v_dryrun = false;
    else
        v_opts = regexp_split_to_array(p_opts, E''(//s*,//s*)|(//s+)'');
        v_trace = (''trace'' = any(v_opts)); 
        v_dryrun = (''dry'' = any(v_opts)) or (''dryrun'' = any(v_opts)); 
    end if;

    select string_agg(format(''DROP FUNCTION %s(%s);'', 
        oid::regproc, pg_get_function_identity_arguments(oid)), E''/n'')
    from pg_proc
    where proname like p_name_mask
    into v_sql;

    if v_sql is not null then
        if v_trace then
            raise notice E''/n%'', v_sql;
        end if;

        if not v_dryrun then
            execute v_sql;
        end if;
    end if;

    return v_sql;
END $$;

select mypg_drop_functions(''fn_dosomething_%'', ''trace dryrun'');