update - postgresql tutorial
Devuelve múltiples campos como un registro en PostgreSQL con PL/pgSQL (6)
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
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.OUTparámetrosOUTse devuelven automáticamente, no es necesaria una instrucciónRETURN.Como los parámetros
OUTson 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 TABLEes en realidad lo mismo que tener un grupo de parámetrosOUTcombinados con elRETURNS 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, agregueLIMIT 1como se demostró.RETURN QUERYes 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í:
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) /* , ... */ )
...
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.
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);
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;
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)