Devolver setof record (tabla virtual) de la función

Este dilema se puede solucionar de diferentes maneras, pero nosotros te compartimos la resolución más completa para nosotros.

Solución:

^{Todas las respuestas existentes anteriormente están desactualizadas o eran ineficientes para empezar.}

Asumiendo que quieres devolver tres integer columnas

Función PL/pgSQL

Así es como lo hace con PL/pgSQL moderno (PostgreSQL 8.4 o posterior):

CREATE OR REPLACE FUNCTION f_foo() -- (open_id numeric) -- parameter not used
  RETURNS TABLE (a int, b int, c int) AS
$func$
BEGIN
RETURN QUERY VALUES
  (1,2,3)
, (3,4,5)
, (3,4,5)
;
END
$func$  LANGUAGE plpgsql IMMUTABLE ROWS 3;

En Postgres 9.6 o posterior también puede agregar PARALLEL SAFE.

Llamada:

SELECT * FROM f_foo();

Puntos principales

• Utilizar RETURNS TABLE para definir un tipo de fila ad-hoc para devolver.
  O RETURNS SETOF mytbl para utilizar un tipo de fila predefinido.

• Utilizar RETURN QUERY para devolver varias filas con un comando.

• Utilizar una VALUES expresión para ingresar múltiples filas manualmente. Este es SQL estándar y ha existido Siempre.

• Si realmente necesita un parámetro, use un nombre de parámetro (open_id numeric) en lugar de ALIAS, que se desaconseja. En el ejemplo no se usó el parámetro y solo ruido…

• No es necesario utilizar comillas dobles para identificadores perfectamente legales. Las comillas dobles solo son necesarias para forzar nombres ilegales (mixed-mayúsculas, caracteres ilegales o palabras reservadas).

• La volatilidad de la función puede ser IMMUTABLEya que el resultado nunca cambia.

• ROWS 3 es opcional, pero como nosotros saber cuántas filas se devuelven, también podríamos declararlo a Postgres. Puede ayudar al planificador de consultas a elegir el mejor plan.

SQL simple

Para un caso simple como este, puede usar una declaración SQL simple en su lugar:

VALUES (1,2,3), (3,4,5), (3,4,5)

O, si desea (o tiene) definir nombres y tipos de columnas específicos:

SELECT *
FROM  (
   VALUES (1::int, 2::int, 3::int)
        , (3, 4, 5)
        , (3, 4, 5)
   ) AS t(a, b, c);

funcion SQL

En su lugar, puede envolverlo en una función SQL simple:

CREATE OR REPLACE FUNCTION f_foo()
   RETURNS TABLE (a int, b int, c int) AS
$func$
   VALUES (1, 2, 3)
        , (3, 4, 5)
        , (3, 4, 5);
$func$  LANGUAGE sql IMMUTABLE ROWS 3;

(Todo esto está probado con postgresql 8.3.7. ¿Tiene una versión anterior? Solo miro su uso de “ALIAS POR $ 1”)

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric)
 RETURNS SETOF RECORD AS $$
DECLARE
 open_id ALIAS FOR $1;
 result RECORD;
BEGIN
 RETURN QUERY SELECT '1', '2', '3';
 RETURN QUERY SELECT '3', '4', '5';
 RETURN QUERY SELECT '3', '4', '5';
END
$$;

Si tiene un registro o variable de fila para devolver (en lugar de un resultado de consulta), use “RETORNO SIGUIENTE” en lugar de “RETORNO DE CONSULTA”.

Para invocar la función, debe hacer algo como:

select * from storeopeninghours_tostring(1) f(a text, b text, c text);

Por lo tanto, debe definir lo que espera que sea el esquema de fila de salida de la función en la consulta. Para evitar eso, puede especificar variables de salida en la definición de la función:

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text)
 RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$
BEGIN
 RETURN QUERY SELECT '1'::text, '2'::text, '3'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
 RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;
END
$$;

(No estoy muy seguro de por qué se requieren las conversiones de texto adicionales … ¿’1′ es un varchar por defecto, tal vez?)

Uso bastante tablas temporales en mis funciones. Debe crear un tipo de devolución en la base de datos y luego crear una variable de ese tipo para devolver. A continuación se muestra un código de muestra que hace exactamente eso.

CREATE TYPE storeopeninghours_tostring_rs AS
(colone text,
 coltwo text,
 colthree text
);

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" () RETURNS setof storeopeninghours_tostring_rs AS
$BODY$
DECLARE
  returnrec storeopeninghours_tostring_rs;
BEGIN
    BEGIN 
        CREATE TEMPORARY TABLE tmpopeninghours (
            colone text,
            coltwo text,
            colthree text
        );
    EXCEPTION WHEN OTHERS THEN
        TRUNCATE TABLE tmpopeninghours; -- TRUNCATE if the table already exists within the session.
    END;
    insert into tmpopeninghours VALUES ('1', '2', '3');
    insert into tmpopeninghours VALUES ('3', '4', '5');
    insert into tmpopeninghours VALUES ('3', '4', '5');

    FOR returnrec IN SELECT * FROM tmpopeninghours LOOP
        RETURN NEXT returnrec;
    END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;


select * from storeopeninghours_tostring()

Te mostramos reseñas y calificaciones

Nos puedes añadir valor a nuestro contenido informacional añadiendo tu experiencia en las referencias.

---

---

Utiliza Nuestro Buscador