CODEWITHSUNDEEP
postgresqlfunction
Jose Luis Estevez
Jose Luis Estevez

Reputation: 63

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

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

Upvotes: 1

Views: 197

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

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.

Upvotes: 1

Related Questions