Using a column of a join for argument the a function table in postgresql

Question

I am join a table with a postgresql function table.

    SELECT * FROM  tb_accounts a,
    (SELECT column1, column2 FROM ft_extra_data(a.id) ) e

And it is throwing me the following error:

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

This would be an example of the table function (this example is to view the definition only):

    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;

I've been doing some research and haven't found anything, is this impossible to do?

Code example: dbfiddle

Thorsten Kettner · Accepted Answer · 2021-02-26 11:00:08Z

1

In order to access table a in the subquery (aka derived table) you must make the join lateral:

SELECT *
FROM  tb_accounts a
CROSS JOIN LATERAL ( SELECT column1, column2 FROM ft_extra_data(a.id) ) e;

Alternatively you can call put the function directly into the FROM clause without a derived table:

SELECT a.*, ft.column1, ft.column2
FROM  tb_accounts a
  CROSS JOIN LATERAL ft_extra_data(a.id) as ft;

In that case the lateral is optional, as ft_extra_data() is defined to return a table.

edited Feb 26, 2021 at 11:00

answered Feb 26, 2021 at 10:20

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using a column of a join for argument the a function table in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related