El bucle a través de las columnas de REGISTRO

Necesito para recorrer tipo RECORD elementos clave o índice, como me puede hacer esto utilizando la matriz de estructuras en otros lenguajes de programación.

Por ejemplo:

DECLARE
    data1    record;
    data2    text;
...
BEGIN
...
FOR data1 IN
    SELECT
        *
    FROM
        sometable
LOOP

    FOR data2 IN
        SELECT
            unnest( data1 )   -- THIS IS DOESN'T WORK!
    LOOP
        RETURN NEXT data1[data2];   -- SMTH LIKE THIS
    END LOOP;

END LOOP;
  • Hay soluciones .. dependiendo de los tipos de datos en uso. Por favor, puedes agregar una tabla típica definición y algunos datos de ejemplo, la forma deseada de salida y la completa definición de la función, incluyendo los parámetros? Es la función de la intención de una tabla o tablas diferentes? Este último requiere de una mano en un nombre de tabla a través del parámetro y el uso de SQL dinámico …
InformationsquelleAutor RKI | 2012-10-25

5 Kommentare

  1. 12

    Como @Pavel explicado, simplemente no es posible pasar a través de un registro, como podría recorrer una matriz. Pero hay varias formas de hacerlo, dependiendo de sus requisitos exactos. En última instancia, ya que desea devolver todos los valores en la misma columna, en la que usted necesita para echarlos al mismo tipo – text el más evidente es el terreno común, porque no es una representación de texto para cada tipo.

    Rápido y sucio

    Decir, tiene una tabla con un integer, un text y un date columna.

    CREATE TEMP TABLE tbl(a int, b text, c date);
    INSERT INTO tbl VALUES
     (1, '1text',     '2012-10-01')
    ,(2, '2text',     '2012-10-02')
    ,(3, ',3,ex,',    '2012-10-03')  -- text with commas
    ,(4, '",4,"ex,"', '2012-10-04')  -- text with commas and double quotes

    A continuación, la solución puede ser tan sencillo como:

    SELECT unnest(string_to_array(trim(t::text, '()'), ','))
    FROM   tbl t;

    Funciona para las dos primeras filas, pero no para los casos especiales de la fila 3 y 4.

    Usted puede resolver el problema con las comas en el texto de la representación:

    SELECT unnest(('{' || trim(t::text, '()') || '}')::text[])
    FROM   tbl t
    WHERE  a < 4;

    Esto podría funcionar bien – excepto para la línea 4, que tiene el doble de la cita en el texto de la representación. Aquellos que se escapó por la duplicación de ellos. Pero el constructor de array tendría les escapó por \. No sé por qué esta incompatibilidad es lo que hay …

    SELECT ('{' || trim(t::text, '()') || '}') FROM tbl t WHERE a = 4

    Rendimientos:

    {4,""",4,""ex,""",2012-10-04}

    Pero necesitaría:

    SELECT '{4,"\",4,\"ex,\"",2012-10-04}'::text[];  -- works

    Solución adecuada

    Si sabía los nombres de columna de antemano, un limpio solución sería sencilla:

    SELECT unnest(ARRAY[a::text,b::text,c::text])
    FROM tbl

    Ya que operan en los registros de bien saber el tipo sólo puede consultar el catálogo de sistema:

    SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
    FROM   pg_catalog.pg_attribute a 
    WHERE  a.attrelid = 'tbl'::regclass
    AND    a.attnum > 0
    AND    a.attisdropped = FALSE

    Poner esto en una función con SQL dinámico:

    CREATE OR REPLACE FUNCTION unnest_table(_tbl text)
      RETURNS SETOF text LANGUAGE plpgsql AS
    $func$
    BEGIN
    
    RETURN QUERY EXECUTE '
    SELECT unnest(ARRAY[' || (
        SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
        FROM   pg_catalog.pg_attribute a 
        WHERE  a.attrelid = _tbl::regclass
        AND    a.attnum > 0
        AND    a.attisdropped = false
        ) || '])
    FROM   ' || _tbl::regclass;
    
    END
    $func$;

    Llamada:

    SELECT unnest_table('tbl') AS val

    Devuelve:

    val
    -----
    1
    1text
    2012-10-01
    2
    2text
    2012-10-02
    3
    ,3,ex,
    2012-10-03
    4
    ",4,"ex,"
    2012-10-04

    Esto funciona sin necesidad de instalar módulos adicionales. Otra opción es instalar el hstore extensión y uso como @Craig demuestra.

    • Gracias! Se ve bien, pero cuando yo no «saber» de la estructura de la tabla, esto no es para mí.
    • Creo que usted entiende mal. La solución final es solo para ese caso. Intenta leer de nuevo.
    • Gracias! Ahora entendí. Voy a probar tu solución en pocos días.
  2. 5

    PL/pgSQL no está realmente diseñado para lo que quieres hacer. No tener en cuenta un registro para ser iterable, es una tupla de, posiblemente, diferentes y tipos de datos incompatibles.

    PL/pgSQL ha EXECUTE para SQL dinámico, pero EXECUTE consultas no se refieren a PL/pgSQL variables como NEW u otros archivos directamente.

    Lo que puede hacer es convertir el registro a un hstore clave/valor de la estructura, luego iterar sobre el hstore. Uso each(hstore(the_record)), que produce un conjunto de filas de key,value tuplas. Todos los valores se convierten a su text representaciones.

    Este juguete función muestra la iteración a través de un registro mediante la creación de un anónimo ROW(..) que los nombres de columna f1, f2, f3 – a continuación, convertir que a hstore, iterando sobre su columna/pares de valor, y volviendo cada par.

    CREATE EXTENSION hstore;
    
    CREATE OR REPLACE FUNCTION hs_demo()
    RETURNS TABLE ("key" text, "value" text)
    LANGUAGE plpgsql AS
    $$
    DECLARE
      data1 record;
      hs_row record;
    BEGIN
      data1 = ROW(1, 2, 'test');
      FOR hs_row IN SELECT kv."key", kv."value" FROM each(hstore(data1)) kv
      LOOP
        "key" = hs_row."key";
        "value" = hs_row."value";
        RETURN NEXT;
      END LOOP;
    END;
    $$;

    Que en realidad nunca iba a escribir de esta manera, ya que todo el bucle puede ser reemplazado con un simple RETURN QUERY declaración y se hace lo mismo que each(hstore) hace de todos modos, así que esta es sólo para mostrar cómo each(hstore(record)) obras, y la función de arriba nunca debe ser utilizada.

    • Muchas gracias! Yo pensando de esta manera.
    • Supongo que esto es lo que Erwin se refiere a create or replace function row_to_jsonarray(r anyelement) returns json language sql immutable AS $$ select to_json(array (select value from each(hstore(r)) ) ); $$ ; Obras en PostgreSQL 9.4. Pero la mente que hstore no garantiza el orden de las columnas en el registro de la misma en el resultado json.
  3. 2

    Esta característica no se admite en plpgsql – Registro NO ES el hash de la matriz como otros lenguajes de scripting – es similar a la de C o ADA, donde esta funcionalidad es imposible. Puede utilizar otros PL lenguaje como PLPerl o PLPython o algunos trucos que usted puede iterar con HSTORE tipo de datos (extensión) o a través de SQL dinámico

    ver Cómo establecer el valor de los compuestos de la variable de campo el uso de SQL dinámico

    Pero a petición de esta funcionalidad que normalmente significa, de modo de hacer algún mal. Cuando el uso de PL/pgSQL que han de pensar diferentes que puedes usar Javascript o Python

    • Gracias! Tengo la sospecha sobre él.
  4. 0
    FOR data2 IN
        SELECT d
        from  unnest( data1 ) s(d)
    LOOP
        RETURN NEXT data2;
    END LOOP;
    • regress=# SELECT unnest(ROW(1,2,'test')); ERROR: function unnest(record) does not exist
    • Sí, él tendría que elegir data1.myArray. Al menos supongo que es una matriz como la que está tratando de unnest es
    • Yo creo que él quiere unnest un registro en pares clave/valor. Que simplemente no es compatible y no tiene sentido en un tipo de modelo relacional, a menos que hagan como hstore ¿y convertir todos los valores de texto de todos modos.
    • Gracias! Pero usted no entiende mi pregunta.
  5. 0

    Si el fin de sus resultados antes de que el bucle, se puede lograr lo que usted desea.

    for rc in select * from t1 order by t1.key asc loop
     return next rc;
    end loop;

    va a hacer exactamente lo que usted necesita. También es la forma más rápida de realizar ese tipo de tarea.

    • Gracias! Pero usted no entiende mi pregunta.

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea