update - postgresql tutorial

Devuelve múltiples campos como un registro en PostgreSQL con PL/pgSQL

Question

Estoy escribiendo un SP, usando PL / pgSQL.
Quiero devolver un registro, compuesto de campos de varias tablas diferentes. Podría verse algo como esto:

CREATE OR REPLACE FUNCTION get_object_fields(name text)
  RETURNS RECORD AS $$
BEGIN
  -- fetch fields f1, f2 and f3 from table t1
  -- fetch fields f4, f5 from table t2
  -- fetch fields f6, f7 and f8 from table t3
  -- return fields f1 ... f8 as a record
END
$$ language plpgsql; 

¿Cómo puedo devolver los campos de diferentes tablas como campos en un solo registro?

[Editar]

Me di cuenta de que el ejemplo que di más arriba era un poco simplista. Algunos de los campos que necesito recuperar se guardarán como filas separadas en la tabla de la base de datos que se está consultando, pero quiero devolverlos en la estructura de registro "aplanada".

El siguiente código debería ayudar a ilustrar más:

CREATE TABLE user (id int, school_id int, name varchar(32));

CREATE TYPE my_type (
  user1_id   int,
  user1_name varchar(32),
  user2_id   int,
  user2_name varchar(32)
);

CREATE OR REPLACE FUNCTION get_two_users_from_school(schoolid int)
  RETURNS my_type AS $$
DECLARE
  result my_type;
  temp_result user;
BEGIN
  -- for purpose of this question assume 2 rows returned
  SELECT id, name INTO temp_result FROM user where school_id = schoolid LIMIT 2;
  -- Will the (pseudo)code below work?:
  result.user1_id := temp_result[0].id ;
  result.user1_name := temp_result[0].name ;
  result.user2_id := temp_result[1].id ;
  result.user2_name := temp_result[1].name ;
  return result ;
END
$$ language plpgsql

Answer 1

Esto puede ser más simple con los parámetros OUT :

CREATE OR REPLACE FUNCTION get_object_fields(
          name text
    ,OUT user1_id   int
    ,OUT user1_name varchar(32)
    ,OUT user2_id   int
    ,OUT user2_name varchar(32)
) AS 
$func$
BEGIN
    SELECT t.user1_id, t.user1_name
    INTO     user1_id,   user1_name
    FROM   tbl1 t
    WHERE  t.tbl1_id = 42;

    user2_id := user1_id + 43; -- some calculation

    SELECT t.user2_name
    INTO     user2_name
    FROM   tbl2 t
    WHERE  t.tbl2_i = user2_id;
END
$func$ LANGUAGE plpgsql;

• No necesita crear un tipo solo por el bien de esta función plpgsql. Puede ser útil si desea vincular un par de funciones del mismo tipo. Raramente lo uso más ya que se agregaron parámetros de OUT .

• Como habrás notado, no hay declaración RETURN . OUT parámetros OUT se devuelven automáticamente, no es necesaria una instrucción RETURN .

• Como los parámetros OUT son 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 con el mismo nombre para evitar conflictos de nombres.

Más simple aún - o devuelve varias filas

La mayoría de las veces esto se puede simplificar aún más. A veces, las consultas en el cuerpo de la función se pueden combinar, lo que generalmente es (no siempre) más rápido. Y puede usar RETURNS TABLE() : introducido con Postgres 8.4 (mucho antes de que se hiciera esta pregunta también).

El ejemplo de arriba podría reescribirse como:

CREATE OR REPLACE FUNCTION get_object_fields(name text)
  RETURNS TABLE (
     user1_id   int
    ,user1_name varchar(32)
    ,user2_id   int
    ,user2_name varchar(32)) AS 
$func$
BEGIN
    RETURN QUERY
    SELECT t1.user1_id, t1.user1_name, t2.user2_id, t2.user2_name
    FROM   tbl1 t1
    JOIN   tbl2 t2 ON t2.user2_id = t1.user1_id + 43
    WHERE  t1.tbl1_id = 42
    LIMIT  1;  -- may be optional
END
$func$ LANGUAGE plpgsql; 

• RETURNS TABLE es en realidad lo mismo que tener un grupo de parámetros OUT combinados con el RETURNS record , solo un poco más cortos / más elegantes.

• La principal diferencia es que esta función puede devolver 0, 1 o muchas filas, mientras que la primera versión siempre devuelve 1 fila.
  Si quiere asegurarse de que este devuelve solo 0 o 1 fila, agregue LIMIT 1 como se demostró.

• RETURN QUERY es la manera moderna muy práctica de devolver resultados de una consulta directamente.
  Puede usar varias instancias en una sola función para agregar más filas a la salida.

Varios tipos de filas

Si se supone que su función devuelve dinámicamente los resultados con diferentes tipos de filas , según la entrada, lea más aquí:

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

Answer 2

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, necesita definir la función como returns setof my_type

Actualizar

Otra opción es usar RETURNS TABLE() 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) /* , ... */ )
...

Answer 3

No use CREAR TIPO para devolver un resultado polimórfico. Utilice y abuse del tipo RECORD en su lugar. 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 devolver dos o tres columnas según 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 una cantidad consistente 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 una cantidad consistente 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 calor é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 en múltiples filas para que su capa de ORM pueda convertir los valores en 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 características más geniales y menos utilizadas en PostgreSQL. Pasa la voz.

Answer 4

Puede lograr esto usando simplemente como un conjunto de registros devueltos usando la consulta de retorno.

   CREATE OR REPLACE FUNCTION schemaName.get_two_users_from_school(schoolid bigint)
     RETURNS SETOF record
     LANGUAGE plpgsql
    AS $function$
    begin

     return query
      SELECT id, name FROM schemaName.user where school_id = schoolid;

    end;
    $function$

Y llame a esta función como: select * from schemaName.get_two_users_from_school(schoolid) as x(a bigint, b varchar);

Answer 5

Si tiene una tabla con este diseño de registro exacto, use su nombre como tipo, de lo contrario deberá declarar el tipo explícitamente:

CREATE OR REPLACE FUNCTION get_object_fields
        (
        name text
        )
RETURNS mytable
AS
$$
        DECLARE f1 INT;
        DECLARE f2 INT;
        …
        DECLARE f8 INT;
        DECLARE retval mytable;
        BEGIN
        -- fetch fields f1, f2 and f3 from table t1
        -- fetch fields f4, f5 from table t2
        -- fetch fields f6, f7 and f8 from table t3
                retval := (f1, f2, …, f8);
                RETURN retval;
        END
$$ language plpgsql; 

Answer 6

puedes hacer esto usando el parámetro OUT y CROSS JOIN

CREATE OR REPLACE FUNCTION get_object_fields(my_name text, OUT f1 text, OUT f2 text)
AS $$
SELECT t1.name, t2.name
FROM  table1 t1 
CROSS JOIN table2 t2 
WHERE t1.name = my_name AND t2.name = my_name;
$$ LANGUAGE SQL;

luego úsalo como una tabla:

select get_object_fields( ''Pending'') ;
get_object_fields
-------------------
(Pending,code)
(1 row)

o

select * from get_object_fields( ''Pending'');
f1    |   f
---------+---------
Pending | code
(1 row)

o

select (get_object_fields( ''Pending'')).f1;
f1
---------
Pending
(1 row)