我有employer一張employee桌子。我有一個連接它們的連接表。

CREATE TABLE employer (id int primary key, name text);
CREATE TABLE employee (id int primary key, name text);
CREATE TABLE employer_employee_join(
    employer_id int REFERENCES employer(id),
    employee_id int REFERENCES employee(id)
);

INSERT INTO employer (id, name) VALUES (1, 'the boss');
INSERT INTO employee (id, name) VALUES (1, 'employee1');
INSERT INTO employee (id, name) VALUES (2, 'employee2');

INSERT INTO employer_employee_join (employer_id, employee_id) VALUES(1, 1);
INSERT INTO employer_employee_join (employer_id, employee_id) VALUES(1, 2);

我的 sql 查詢回傳employer并聚合employee回傳一組記錄 ( record[])。

SELECT 
employer.id, employer.name, array_agg((employee.id, employee.name))
FROM employer
LEFT JOIN employer_employee_join
ON employer_employee_join.employer_id = employer.id
LEFT JOIN employee
ON employee.id = employer_employee_join.employee_id
GROUP BY employer.id;

這作業正常。

Postgresql - 從 plpgsql 函式回傳一個記錄 []

但是當我把它放在 PL/PGSQL 函式中時，它失敗了：

CREATE OR REPLACE FUNCTION _test()
    RETURNS table(id integer, name text, agg record[]) 
    LANGUAGE 'plpgsql'
AS $BODY$
begin
    SELECT 
    employer.id, employer.name, array_agg((employee.id, employee.name))
    FROM employer
    LEFT JOIN employer_employee_join
    ON employer_employee_join.employer_id = employer.id
    LEFT JOIN employee
    ON employee.id = employer_employee_join.employee_id
    GROUP BY employer.id;
end;
$BODY$

錯誤是

ERROR:  PL/pgSQL functions cannot accept type record[]
SQL state: 0A000

我怎樣才能得到一個 plpgsql 函式來回傳一個記錄陣列？

（我真的不想使用，json_agg()因為系統中的另一個層在 postgresql 和我的控制之外）

uj5u.com熱心網友回復：

感謝@Bergi。這是我需要的復合型別。

create type employee_agg as (id int, name text);

CREATE OR REPLACE FUNCTION _test()
    RETURNS table(id integer, name text, agg employee_agg[]) 
    LANGUAGE plpgsql
AS $BODY$
begin
    return query
    SELECT 
    employer.id, 
    employer.name, 
    array_agg(row(employee.id, employee.name)::employee_agg)
    FROM employer
    LEFT JOIN employer_employee_join
    ON employer_employee_join.employer_id = employer.id
    LEFT JOIN employee
    ON employee.id = employer_employee_join.employee_id
    GROUP BY employer.id;
end;
$BODY$;

uj5u.com熱心網友回復：

使用視圖

就這樣做...

CREATE VIEW _test
AS
    SELECT 
    employer.id, 
    employer.name, 
    array_agg(row(employee.id, employee.name)::employee_agg)
    FROM employer
    LEFT JOIN employer_employee_join
    ON employer_employee_join.employer_id = employer.id
    LEFT JOIN employee
    ON employee.id = employer_employee_join.employee_id
    GROUP BY employer.id;

您的函式沒有引數，它只是一個查詢。這是一個更好的主意。

或者，您可以使用SQL函式（但視圖更好）。

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/363754.html

標籤：PostgreSQL的 plpgsql

上一篇：使用psycopg2批量插入多行

下一篇：使用psycopg2將整數串列插入postgres表

Postgresql-從plpgsql函式回傳一個記錄[]

使用視圖