Crear función en PostgreSQL para actualizar los valores de columna de una tabla con valores preferidos y alias

votos
1

Quiero crear una función que va a actualizar una columna del tipo varcharde una cadena preferido que se hace referencia en la columna de otra tabla para ayudar a limpiar esta columna más iterativa.

CREATE TABLE big_table (
    mn_uid NUMERIC PRIMARY KEY,
    user_name VARCHAR
    );

INSERT INTO big_table VALUES
        (1, 'DAVE'),
        (2, 'Dave'),
        (3, 'david'),
        (4, 'Jak'),
        (5, 'jack'),
        (6, 'Jack'),
        (7, 'Grant'); 

CREATE TABLE nameKey_table (
    nk_uid NUMERIC PRIMARY KEY,
    correct VARCHAR,
    wrong VARCHAR
    );

INSERT INTO nameKey_table VALUES
        (1, 'David', 'Dave_DAVE_dave_DAVID_david'),
        (2, 'Jack', 'JACK_jack_Jak_jak');

Quiero realizar el siguiente procedimiento:

UPDATE big_table
SET user_name = (SELECT correct
                 FROM nameKey_table 
                 WHERE wrong 
                 LIKE '%DAVE%')
WHERE user_name = 'DAVE';

pero en bucle sobre cada uno user_namede big_tablemodo que tenga una función que puede hacer algo como esto:

UPDATE big_table SET user_name = corrected_name_fn();

Aquí está mi intento de hacer algo como esto, pero me parece que no puede conseguir que funcione:

CREATE FUNCTION corrected_name_fn() RETURNS VARCHAR AS $$
DECLARE entry RECORD;
DECLARE correct_name VARCHAR;
BEGIN 
FOR entry IN SELECT DISTINCT user_name FROM big_table LOOP
     EXECUTE 'SELECT correct 
              FROM nameKey_table
              WHERE wrong 
              LIKE ''%$1%''' 
              INTO correct_name
              USING entry;
            RETURN correct_name;
            END LOOP;
END;    
$$ LANGUAGE plpgsql;

Quiero que la salida final en big_tableser:

| mn_uid |  user_name |
|   1    | 'David'    |
|   2    | 'David'    |
|   3    | 'David'    |
|   4    | 'Jack'     |
|   5    | 'Jack'     |
|   6    | 'Jack'     |
|   7    | 'Grant'    |

Soy consciente de las filas 6 y 7 proporcionan dos casos singulares que quiero incorporar en la función con IF ELSEdeclaraciones.

  1. Si user_nameestá en nameKey_table.correct, vaya al siguiente
  2. Si user_nameno está en nameKey_table.correcto no coincide con una cadena en nameKey_table.wrong, dejar como está.

¡¡Gracias por cualquier ayuda en esto!!

Publicado el 19/09/2018 a las 17:09
fuente por usuario
En otros idiomas...                            


2 respuestas

votos
0

Parece que usted quiere un disparador en la tabla. Aquí está mi sugerencia:

CREATE OR REPLACE FUNCTION tf_fix_name() RETURNS TRIGGER AS
$$
DECLARE
    corrected_name TEXT;
BEGIN

    SELECT correct INTO corrected_name FROM nameKey_table WHERE expression ~* NEW.user_name;
    IF FOUND THEN
        NEW.user_name := corrected_name;
    END IF;

    RETURN NEW;
END;
$$
    LANGUAGE plpgsql;


CREATE TEMP TABLE big_table (
    mn_uid INT PRIMARY KEY,
    user_name TEXT NOT NULL
);

CREATE TRIGGER trigger_fix_name
    BEFORE INSERT
    ON big_table
    FOR EACH ROW
    EXECUTE PROCEDURE tf_fix_name();

CREATE TEMP TABLE nameKey_table (
    nk_uid INT PRIMARY KEY,
    correct TEXT NOT NULL,
    expression TEXT NOT NULL
    );

INSERT INTO nameKey_table VALUES
        (1, 'David', '(dave|david)'),
        (2, 'Jack', '(jack|jak)');

INSERT INTO big_table VALUES
        (1, 'DAVE'),
        (2, 'Dave'),
        (3, 'david'),
        (4, 'Jak'),
        (5, 'jack'),
        (6, 'Jack'),
        (7, 'Grant');

SELECT * FROM big_table;

+--------+-----------+
| mn_uid | user_name |
+--------+-----------+
|      1 | David     |
|      2 | David     |
|      3 | David     |
|      4 | Jack      |
|      5 | Jack      |
|      6 | Jack      |
|      7 | Grant     |
+--------+-----------+
(7 rows)

Nota: Yo creo que se puede hacer lo que quiere mucho más fácil con una expresión regular insensible. Y también he cambiado sus claves primarias de intercepciones. No estoy seguro de por qué son los valores numéricos, pero en realidad no cambia las soluciones. Mi solución fue desarrollado y probado en PostgreSQL 9.6.

Respondida el 19/09/2018 a las 18:37
fuente por usuario

votos
0

No es necesario una función; que sólo puede actualizar una tabla a partir de los contenidos de otra tabla:


UPDATE big_table dst
SET user_name = src.correct
FROM nameKey_table src
WHERE src.wrong LIKE '%' || dst.user_name || '%'
AND dst.user_name <> src.correct -- avoid idempotent updates
        ;

Y si necesita el rendimiento, no te confiar en el LIKEoperador, no se puede utilizar índices de dirigir %. En su lugar, utilice una tabla de consulta con una entrada por fila:


CREATE TABLE bad_spell (
    correct VARCHAR,
    wrong VARCHAR PRIMARY KEY -- This will cause an unique index to be created.
    );

INSERT INTO bad_spell VALUES
        ('David', 'Dave')
        ,('David', 'DAVE')
        ,('David', 'dave')
        ,('David', 'DAVID')
        ,('David', 'david')
        ,('Jack', 'JACK')
        ,('Jack', 'jack')
        ,('Jack', 'Jak')
        ,('Jack', 'jak')
        ;
-- This indexes could be temporary
CREATE INDEX ON big_table(user_name);

-- EXPLAIN
UPDATE big_table dst
SET user_name = src.correct
FROM bad_spell src
WHERE dst.user_name = src.wrong
AND dst.user_name <> src.correct -- avoid idempotent updates
        ;

SELECT* FROM big_table
        ;
Respondida el 19/09/2018 a las 18:05
fuente por usuario

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more