将联接结果传递给记录并删除重复项

nike
id - name - editor_id

adidas
id - name - editor_id

我想做的是根据具体情况选择所有的耐克和阿迪达斯 editor_id . 结果集的格式必须为:

id(nike or adidas) - name(nike or adidas) - editor_id - isNike

isNike 是布尔值,所以如果true是nike,如果false是adidas。

编辑id ,将它们放在特定的格式中,添加布尔标志,避免双精度并返回。

CREATE  FUNCTION myfunction(id bigint)
RETURNS TABLE(id bigint, name text, editor_id bigint, isNike boolean) 
AS $$
DECLARE
query text;
firstrecord record;
BEGIN    

    query := '
    SELECT
    nike.name, nike.id, nike.editor_id,
    adidas.name, adidas.id, adidas.editor_id
    FROM
    adidas left join nike
    ON nike.editor_id = adidas.editor_id
    WHERE
    nike.editor_id ';

        FOR myrecord IN 
        EXECUTE format(query)  USING id
         LOOP
           IF myrecord.adidas.id IS NOT NULL THEN
             RETURN QUERY VALUES(myrecord.adidas.id, myrecord.adidas.name, myrecord.adidas.editor_id, false);  
           END IF;
           IF myrecord.pid IS NOT NULL THEN
              RETURN QUERY VALUES(myrecord.nike.id, myrecord.nike.name, myrecord.nike.editor_id, true);  
           END IF;
        END LOOP; 


    RETURN; 
END;
$$
LANGUAGE plpgsql;

这很有效,但我的简历有两倍。我试过各种组合 select distinct left join , inner join 或 cross join 但我还是有双打,比如:

id - name - editor_id - isNike
1 - aaa - 3 - true
43 - bbb - 9 - false
5 - ccc - 58 - true
1 - aaa - 3 - true --double

我该怎么解决?

谢谢你