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

Question

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?

nachospiu · Accepted Answer · 2021-09-18 11:45:27Z

1

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;

answered Sep 18, 2021 at 11:45

nachospiu

2,0792 gold badges10 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

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

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