Search code examples
sqlpostgresqlpostgresql-13

How to select from a Postgresql function that requires a table as argument

I have a function that requires a table as input, and returns the same table.

CREATE TABLE tab1
    (`id` int, `a` int, `b` int, `c` varchar(16), `timestamp` datetime)
;

CREATE OR REPLACE FUNCTION func(sp "tab1")
    RETURNS TABLE (
        LIKE "tab1")
    LANGUAGE 'plpython3u'
    AS [...];

I want to call it similar to this:

SELECT * from func()

However, this function also requires a table as argument. Because of that, I always called it like this:

SELECT func (t) FROM "table" AS t WHERE id = 1;

but it returned a single column with (1,NULL,NULL,SOMERANDOMTEXT,"2021-09-17 20:23:22.788179+00")

How can I make the SELECT return separate columns instead of a single func column?

Solution

  • This work for me:

    SELECT (func(t.*)).* FROM tab1 AS t WHERE t.id = 1

    You could use LATERAL JOIN too:

    SELECT t.*
FROM tab1,
      LATERAL func(tab1) AS t
WHERE tab1.id = 1;