Postgres function Return Select * result from multiple tables

Question

I need to return "select * from table1 t1 join table2 t2 on t1.id = t2.t2id where t1.id = _id" using function get_data(_id).

I fount solution: https://stackoverflow.com/a/11751557 But there is result from one table, and I can't understand is this possible to add join to this query to get result from multiple table?

I tried:

CREATE OR REPLACE FUNCTION get_data_test_2(_tbl_type anyelement,_tbl_type_2 anyelement, _id int)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM  %s as s1  -- pg_typeof returns regtype, quoted automatically
      left join %s as s2 on s1.id = s2.t1id
      WHERE  id = s1.'||_id||''
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;

But obviously it's not working UPD Main problem that I need to get all columns from joined tables dynamically without setting explicit columns names.

Ok, then why all that ado with `pg_typeof`? Just pass the table names (or `regclass`). — Laurenz Albe, Oct 25 '21 at 14:38
First comment : your function get_data_test2 is a polymorphic function using the polymorphic type anyelement, but in this case the output parameter type will be deducted from the input parameter type, which seems not to be the case in your function as the result will be a set of records. You should instead define the data type of your input data and define the output data type with 'RETURNS SETOF record' — Edouard, Oct 25 '21 at 14:38
Because I can't understand how to return results (select *) without setting column names and type in function call. This solution [link](https://stackoverflow.com/a/11751557) helped me a lot, because I can get result dynamically(?). I need to do this dynamically because sometimes there can be table changes and this is to hard for me to replace function every time this happens. — FeoJun, Oct 25 '21 at 14:54

score 0 · Answer 1 · answered Oct 29 '21 at 21:40

You can try this :

CREATE OR REPLACE FUNCTION get_data_test_2(_tbl_type anyelement,_tbl_type_2 anyelement, _id int)
  RETURNS SETOF record
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM  %s as s1
      left join %s as s2 on s1.id = s2.t1id
      WHERE  id = s1.'||_id
    , pg_typeof(_tbl_type)
    , pg_typeof(_tb2_type))
   USING  _id;
END
$func$;

Postgres function Return Select * result from multiple tables

1 Answers1