我正在连接一个表和一个postgresql函数表。
SELECT * FROM tb_accounts a,
(SELECT column1, column2 FROM ft_extra_data(a.id) ) e
显示如下错误:
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 4: ft_extra_data(a.id)
^
HINT: There is an entry for table "a", but it cannot be referenced from this part of the query.
SQL state: 42P01
Character: 153
这是一个表函数的例子(这个例子只查看定义):
CREATE OR REPLACE FUNCTION ft_extra_data(IN p_account_id bigint)
RETURNS TABLE(
column1 character varying, column2 character varying) AS
$BODY$
DECLARE
BEGIN
RETURN QUERY
SELECT 'xxx' as column1, 'yyy' as column2;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
我一直在做一些研究,但没有发现任何东西,这是不可能的吗?
代码示例:dbfiddle
为了访问子查询(也称为派生表)中的表a
,您必须使连接横向:
SELECT *
FROM tb_accounts a
CROSS JOIN LATERAL ( SELECT column1, column2 FROM ft_extra_data(a.id) ) e;
或者您可以直接调用put函数到FROM子句中,而不需要派生表:
SELECT a.*, ft.column1, ft.column2
FROM tb_accounts a
CROSS JOIN LATERAL ft_extra_data(a.id) as ft;
在这种情况下lateral
是可选的,因为ft_extra_data()
被定义为返回一个表。