computed relations from view to view foreign key error

[MHC2000]

I have the view dt_72 on my api schema, which exposes a table from another schema to the users.
Now I wanted to join some additional computed data from a view to this data and because there is no direct foreign key I wanted to use the computed relation dt_72_test_computed_data for that.
Which links the data to the view in software.infoapd_smv2v_def. The relation is one-to-many.

if I query dt_72?select=,dt_72_test_computed_data() I get the PGRST200 error. Schema cache was reloaded. What am I doing wrong?
Or is it not possible to compute relations to another view?
Weirdly dt_72?select=*,dt_72_test_computed_data works, but only delivers an object and not an array for dt_72_test_computed_data

{
  "code": "PGRST200",
  "details": "Searched for a foreign key relationship between 'dt_72' and 'dt_72_test_computed_data' in the schema 'api', but no matches were found.",
  "message": "Could not find a relationship between 'dt_72' and 'dt_72_test_computed_data' in the schema cache"
}

-- View: api.dt_72

-- DROP VIEW api.dt_72;

CREATE OR REPLACE VIEW api.dt_72
 AS
 SELECT mt.id,
    mt.is_active,
    mt.created,
    mt.modified,
    mt.state,
    mt.creator,
    mt.description,
    mt.import_data,
    mt.version,
    mt.title,
    mt.v_id,
    mt.variant_title
   FROM software.a_param_deliveries mt
  WHERE AND mt.is_active = true;

-- computed relation
CREATE OR REPLACE FUNCTION api.dt_72_test_computed_data(
	api.dt_72)
    RETURNS SETOF software.infoapd_smv2v_def 
    LANGUAGE 'sql'
    COST 100
    STABLE PARALLEL SAFE 
    ROWS 1000

AS $BODY$
  select * from software.infoapd_smv2v_def WHERE a_param_delivery = $1.id
$BODY$;

-- the linked view
CREATE OR REPLACE VIEW software.infoapd_smv2v_def
 AS
 SELECT ssmv2vd.id,
    ssmv2vd.created,
    ssmv2vd.modified,
    ssmv2vd.is_active,
    sapd.id AS a_param_delivery,
    ssmv2vd2dmr.id_dmr as dmr,
    mddmr.plo,
    mddmr.plo_sub
   FROM software.smv2v_def ssmv2vd
     JOIN software.a_param_deliveries sapd 
		ON sapd.version = ssmv2vd.version 
			AND sapd.v_id = ssmv2vd.v_id
     JOIN software.smv2v_def2dmr ssmv2vd2dmr 
		ON ssmv2vd2dmr.smv2v_def = ssmv2vd.id 
			AND ssmv2vd2dmr.is_active = true
     LEFT JOIN master_data.dmr mddmr 
		ON mddmr.id = ssmv2vd2dmr.dmr
  WHERE ssmv2vd.is_active = true;

[laurenceisla]

f I query dt_72?select=,dt_72_test_computed_data() I get the PGRST200 error. Schema cache was reloaded. What am I doing wrong?

I think it's because the function is returning a view that is not present in the exposed schema.

    RETURNS SETOF software.infoapd_smv2v_def 

If it were returning a table or view in the api schema then it would work. You may need to create a similar view on that schema, taking care of not exposing secure data, of course.

Weirdly dt_72?select=*,dt_72_test_computed_data works, but only delivers an object and not an array for dt_72_test_computed_data

Yes, that's because it's treating it as a computed column (not relationship) when no () is specified.

[MHC2000]

Thanks for the help. The exposing of the view was the solution.
I added a view in the exposed schema and the function returns this view as result.
The error is now gone.

But now I just get an empty array as result.
How can I check the function at a database level if it's working?

[laurenceisla]

Hmm... maybe Row Level Security is doing something there. Anyways, you can verify the query in your PostgreSQL logs. This can help you if you don't have them activated: https://postgrest.org/en/latest/references/observability.html#database-logs

[MHC2000]

Found the error, was a missing link!
Thanks a lot for the help