如何在PostgreSQL函数中关联SQL查询返回的表与另一张表？

要实现先过滤表db.x再与db.y内连接，需要调整SQL语句的结构，把INNER JOIN放在正确位置，同时合并过滤条件。以下是两种可行的实现方式：

方式1：仅返回表X的列（保留原返回结构） #

如果只需要返回表db.x的列，但通过表db.y的player_id过滤数据，可将连接条件和过滤条件整合：

CREATE OR REPLACE FUNCTION player_data.GetAllTransactions(playerId INTEGER, transactionDate DATE)
RETURNS TABLE(id BIGINT, created_date TIMESTAMP WITH TIME ZONE, financial_transaction_type_id SMALLINT, amount NUMERIC)
LANGUAGE 'plpgsql'
AS
$$
BEGIN
    RETURN QUERY 
        SELECT db.x.id, db.x.created_date, db.x.trans_type, db.x.amount 
        FROM db.x
        INNER JOIN db.y ON db.y.id = db.x.wallet_id
        WHERE db.x.created_date::DATE = transactionDate
          AND db.y.player_id = playerId;
END;
$$

方式2：返回包含表X和表Y的列 #

如果需要返回两表的列，需先修改RETURNS TABLE的定义，添加表db.y对应的列（示例假设db.y有wallet_name列，可根据实际调整）：

CREATE OR REPLACE FUNCTION player_data.GetAllTransactions(playerId INTEGER, transactionDate DATE)
RETURNS TABLE(
    transaction_id BIGINT, 
    created_date TIMESTAMP WITH TIME ZONE, 
    trans_type SMALLINT, 
    amount NUMERIC,
    wallet_id INT,
    wallet_name VARCHAR
)
LANGUAGE 'plpgsql'
AS
$$
BEGIN
    RETURN QUERY 
        SELECT 
            db.x.id AS transaction_id, 
            db.x.created_date, 
            db.x.trans_type, 
            db.x.amount,
            db.y.id AS wallet_id,
            db.y.wallet_name
        FROM db.x
        INNER JOIN db.y ON db.y.id = db.x.wallet_id
        WHERE db.x.created_date::DATE = transactionDate
          AND db.y.player_id = playerId;
END;
$$

关键说明 #

• INNER JOIN必须写在FROM子句之后、WHERE子句之前，这是SQL标准语法，原代码把JOIN放在WHERE之后属于语法错误。
• 也可以将db.y.player_id = playerId整合到ON子句中，作为连接条件的一部分，效果和放在WHERE中一致：

  INNER JOIN db.y ON db.y.id = db.x.wallet_id AND db.y.player_id = playerId
  

内容的提问来源于stack exchange，提问作者Meeth46