Devuelve varios campos como un registro en PostgreSQL con PL / pgSQL

Solución:

No use CREATE TYPE para devolver un resultado polimórfico. En su lugar, use y abuse del tipo RECORD. Echale un vistazo:

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
  -- Arbitrary expression to change the first parameter
  IF LENGTH(a) < LENGTH(b) THEN
      SELECT TRUE, a || b, 'a shorter than b' INTO ret;
  ELSE
      SELECT FALSE, b || a INTO ret;
  END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Preste atención al hecho de que opcionalmente puede regresar dos o Tres columnas dependiendo de la entrada.

test=> SELECT test_ret('foo','barbaz');
             test_ret             
----------------------------------
 (t,foobarbaz,"a shorter than b")
(1 row)

test=> SELECT test_ret('barbaz','foo');
             test_ret             
----------------------------------
 (f,foobarbaz)
(1 row)

Esto causa estragos en el código, así que use un número constante de columnas, pero es ridículamente útil para devolver mensajes de error opcionales con el primer parámetro que devuelve el éxito de la operación. Reescrito utilizando un número constante de columnas:

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
  -- Note the CASTING being done for the 2nd and 3rd elements of the RECORD
  IF LENGTH(a) < LENGTH(b) THEN
      ret := (TRUE, (a || b)::TEXT, 'a shorter than b'::TEXT);
  ELSE
      ret := (FALSE, (b || a)::TEXT, NULL::TEXT);
   END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Casi al picor épico:

test=> SELECT test_ret('foobar','bar');
   test_ret    
----------------
 (f,barfoobar,)
(1 row)

test=> SELECT test_ret('foo','barbaz');
             test_ret             
----------------------------------
 (t,foobarbaz,"a shorter than b")
(1 row)

Pero, ¿cómo se divide eso en varias filas para que la capa de ORM de su elección pueda convertir los valores a los tipos de datos nativos de su idioma de elección? El picor:

test=> SELECT a, b, c FROM test_ret('foo','barbaz') AS (a BOOL, b TEXT, c TEXT);
 a |     b     |        c         
---+-----------+------------------
 t | foobarbaz | a shorter than b
(1 row)

test=> SELECT a, b, c FROM test_ret('foobar','bar') AS (a BOOL, b TEXT, c TEXT);
 a |     b     | c 
---+-----------+---
 f | barfoobar | 
(1 row)

Esta es una de las funciones más interesantes y menos utilizadas de PostgreSQL. Por favor corre la voz.

Necesita definir un nuevo tipo y definir su función para devolver ese tipo.

CREATE TYPE my_type AS (f1 varchar(10), f2 varchar(10) /* , ... */ );

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
RETURNS my_type 
AS 
$$

DECLARE
  result_record my_type;

BEGIN
  SELECT f1, f2, f3
  INTO result_record.f1, result_record.f2, result_record.f3
  FROM table1
  WHERE pk_col = 42;

  SELECT f3 
  INTO result_record.f3
  FROM table2
  WHERE pk_col = 24;

  RETURN result_record;

END
$$ LANGUAGE plpgsql; 

Si desea devolver más de un registro, debe definir la función como returns setof my_type

---

Actualizar

Otra opción es usar RETURNS TABLE() en lugar de crear un TYPE que se introdujo en Postgres 8.4

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
  RETURNS TABLE (f1 varchar(10), f2 varchar(10) /* , ... */ )
...

Para devolver una sola fila

Más simple con OUT parámetros:

CREATE OR REPLACE FUNCTION get_object_fields(_school_id int
                                       , OUT user1_id   int
                                       , OUT user1_name varchar(32)
                                       , OUT user2_id   int
                                       , OUT user2_name varchar(32)) AS 
$func$
BEGIN
   SELECT INTO user1_id, user1_name
          u.id, u.name
   FROM   users u
   WHERE  u.school_id = _school_id
   LIMIT  1;  -- make sure query returns 1 row - better in a more deterministic way?

   user2_id := user1_id + 1; -- some calculation

   SELECT INTO user2_name
          u.name       
   FROM   users u
   WHERE  u.id = user2_id;
END
$func$  LANGUAGE plpgsql;

Llama:

SELECT * FROM get_object_fields(1);

• Tu no necesitar para crear un tipo solo por el bien de esta función plpgsql. Eso mayo será útil si desea vincular varias funciones al mismo tipo compuesto. Demás, OUT los parámetros hacen el trabajo.

• No hay RETURN declaración. OUT los parámetros se devuelven automáticamente con este formulario que devuelve una sola fila. RETURN es opcional.

• Ya que OUT los parámetros están visibles en todas partes dentro del cuerpo de la función (y se pueden usar como cualquier otra variable), asegúrese de calificar las columnas del mismo nombre para la tabla para evitar conflictos de nombres. (Mejor aún, use nombres distintos para empezar).

Más simple aún, también para devolver filas 0-n

Normalmente, esto puede ser más simple y rápido si se pueden combinar las consultas en el cuerpo de la función. Y tú pueden usar RETURNS TABLE() (desde Postgres 8.4, mucho antes de que se hiciera la pregunta) para devolver 0-n filas.

El ejemplo anterior se puede escribir como:

CREATE OR REPLACE FUNCTION get_object_fields2(_school_id int)
  RETURNS TABLE (user1_id   int
               , user1_name varchar(32)
               , user2_id   int
               , user2_name varchar(32)) AS 
$func$
BEGIN
   RETURN QUERY
   SELECT u1.id, u1.name, u2.id, u2.name
   FROM   users u1
   JOIN   users u2 ON u2.id = u1.id + 1
   WHERE  u1.school_id = _school_id
   LIMIT  1;  -- may be optional
END
$func$  LANGUAGE plpgsql;

Llama:

SELECT * FROM get_object_fields2(1);

• RETURNS TABLE es efectivamente lo mismo que tener un montón de OUT parámetros combinados con RETURNS SETOF record, solo más corto.

• La principal diferencia: esta función puede devolver 0, 1 o muchas filas, mientras que la primera versión siempre devuelve 1 fila.
  Agregar LIMIT 1 como se demostró para permitir solo 0 o 1 fila.

• RETURN QUERY es una forma sencilla de devolver los resultados de una consulta directamente.
  Puede usar varias instancias en una sola función para agregar más filas a la salida.

db <> violín aquí (demostrando ambos)

Variando el tipo de fila

Si se supone que su función devuelve resultados dinámicamente con un tipo de fila diferente dependiendo de la entrada, lea más aquí:

• Refactorice una función PL / pgSQL para devolver el resultado de varias consultas SELECT

---

---

Utiliza Nuestro Buscador