0

I have written a SQL function in PostgreSQL that accesses data from another table. On running the function, I am getting following error

relation table2 does not exist postgres

Here is the function that I am creating

CREATE OR REPLACE FUNCTION func(tbl1 table1)
RETURNS TABLE(a int, b text, c int, d text) AS $$
    SELECT a, b, c, d
    FROM table2
    WHERE id = tbl1.user_id;
$$ 
language sql stable;

Working in case I change table2 to myschema.table2

What do I do? I do not want to add schema into the query. I want it to take whatever schema the function is in.

Vinit Khandelwal
  • 490
  • 8
  • 20

1 Answers1

0

imitate 38.5.3. SQL Functions on Composite Types (https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS)

db fiddle

CREATE OR REPLACE FUNCTION func(table1)
RETURNS TABLE(a int, b text, c int, d text) AS $$
SELECT a, b, c, d FROM table2 WHERE id = $1.user_id;
$$
language sql;

call it. select (func(table1.*)).* from table1;

jian
  • 4,119
  • 1
  • 17
  • 32
  • This is not the solution to my problem. I do not have problem accessing input parameters within the function. I have this error: relation \"table2\" does not exist" – Vinit Khandelwal Mar 29 '22 at 07:22
  • then maybe the user/role cannot access the table. most likely because of schema issue. – jian Mar 29 '22 at 08:14
  • 1
    table2 refer to public. table2. public is your default search path. then table2 is not the same as myschema.table2. Yes, you can set the search_path add myschema. But it's better just use myshema.table2. – jian Mar 29 '22 at 09:07
  • 1
    @VinitKhandelwal `SET search_path TO myschema, public;` better is full reference `myschema.table2`. It's werid, you query data cross table.... – jian Mar 29 '22 at 09:11
  • That worked. I set it in the properties – Vinit Khandelwal Mar 29 '22 at 13:13