Resource embedding on procedure

[u8sand]

I have a procedure which facilitates a search based on some query through several paths. The result is a primary key related to another table, but also an additional field for reporting the similarity. I was hoping to take advantage of resource embedding with this procedure but I can't seem to get the embedding to work when I add the additional column..

Here is the procedure I'm working on -- it performs a search through several tables with one query and returns results. If I just return the results (for the table api.path) the embedding works fine as if it was a filter of the api.path table, but if I want to add an additional column (with similarity score) I have to forfeit embedding from working at all.

create or replace function api.query(q text, lim int)
-- <<<<
-- this works:
-- returns setof api.path
-- as $$
--   select
--     api.path.*
-- ====
-- this does not
returns table (
  path varchar, -- primary key of table (i've also tried using all the columns with this additional reason column)
  reason varchar -- description of search results
)
as $$
  select
    api.path.path,
    result.reason
-- >>>>
  from
    (
      with
        -- all of these tables have foreign key relationships to `api.path.path`
        searches as (
          -- search for similar names
          (select
            api.path.path,
            similarity(api.path.name, q) * 0.1 as sim,
            format('name (%s)', round(similarity(api.path.name, q)::numeric, 3)::text) as reason
          from api.path
          -- use when db is large
          --where api.path.name % q
          order by sim desc
          limit lim)
        union
          -- search for similar content
          (select
            api.search.path,
            ts_rank_cd(api.search.tsvec, plainto_tsquery(q), 32) * 10. as sim,
            format('content (%s)', round(ts_rank_cd(api.search.tsvec, plainto_tsquery(q), 32)::numeric, 3)::text) as reason
          from api.search
          -- use when db is large
          --where api.search.tsvec @@ plainto_tsquery(q)
          order by sim desc
          limit lim)
        union
          -- search for similar tags
          (select
            api.tag.path,
            max(similarity(api.tag.tag, q)) * 0.25 as sim,
            format('tag (%s)', round((max(similarity(api.tag.tag, q)) * 0.25)::numeric, 3)::text) as reason
          from api.tag
          -- use when db is large
          --where api.tag.tag % q
          group by api.tag.path
          order by sim desc
          limit lim)
        )
      select
        searches.path,
        string_agg(searches.reason, ', ') as reason
      from searches
      where sim > 0
      group by searches.path
      order by sum(coalesce(sim, 0)) desc
    ) result
    -- join with api.path in an attempt to recover resource for embedding
    --   in reality, i should just be able to use `searches.path`
    inner join api.path on result.path = api.path.path
$$ language sql immutable security definer;

Ideally I could use this like:

< /rpc/query?q=my+specific+query&lim=5&select=path(*)
> [{ "path": {...}, "reason": "tag (0.33)" }, ...]

This primary key is also referenced in many other tables, thus getting this to work would allow me to embed many other tables as well from this endpoint (which is desired).

Can someone more familiar with either postgrest or postgres point me in the right direction? Perhaps it's a flaw in my procedure design or something else. Thank you.

[u8sand]

Edit: See comments for full answer

Update

I played around with this a bit and made some meaningful progress by doing the following (as weird as I felt it was when I tried it).

I created a "placeholder" view to effectively represent the structure I hoped to return so that I can still use setof notation to describe the results:

create or replace view api.query_results as
select *, '' as reason
from api.path;

Un-intuitively, this did allow me to change the reason from an empty string to the reason I wanted:

create or replace function api.query(q text, lim int)
returns setof api.query_results
as $$
  select
    -- all fields in api.path
    api.query_results.path,
    api.query_results.type,
    api.query_results.name,
    -- replace reason
    result.reason
  from
-- ...
    inner join api.query_results on result.path = api.query_results.path
$$ language sql immutable security definer;

Amazingly this version works albeit with the unexpected side-effect of appearing in a list (perhaps this is expected, I'm just new to postgrest -- but api.file has a one-to-one relationship with api.path, both have a primary key path).

< http://localhost:3000/rpc/query?q=test&lim=5&select=reason,file(*)
> [{"reason":"name (0.625)","file":[{"path":"test.md","hashes": ...}]]

Some things that were still unexpected:

1. i tried first with only path (the primary key) but it refused to even offer the api.path table the view is derived from as a possible embedding
2. using select=*,file(*) causes: column reference \"path\" is ambiguous but even select=path,file(*) works

If anyone can offer me some clarity as to why this was necessary and why some aspects of this are not as I'd expect I'd greatly appreciate it. Thank you

[wolfgangwalther]

2\. using `select=*,file(*)` causes: `column reference \"path\" is ambiguous`  but even `select=path,file(*)` works

This sounds very much like a bug that had been fixed in the past, although I can't find the issue / PR right now. Which PostgREST version are you running? Can you try with the latest nightly, too?

[u8sand]

Thanks for the help!

I tried adding a foreign key self referencing the table but it doesn't seem to work seemingly because now api.path & api.query_result are ambiguous:

/rpc/query?q=test&lim=5&select=path,path(*)

{"hint":"By following the 'details' key, disambiguate the request by changing the url to /origin?select=relationship(*) or /origin?select=target!relationship(*)","details":[{"relationship":"path_path_fkey[path][path]","origin":"api.pgrst_source","cardinality":"m2o","target":"api.path"},{"relationship":"path_path_fkey[path][path]","origin":"api.pgrst_source","cardinality":"o2m","target":"api.path"},{"relationship":"path_path_fkey[path][path]","origin":"api.pgrst_source","cardinality":"m2o","target":"api.query_result"},{"relationship":"path_path_fkey[path][path]","origin":"api.pgrst_source","cardinality":"o2m","target":"api.query_result"},{"relationship":"file_path_fkey[path][path]","origin":"api.pgrst_source","cardinality":"o2m","target":"api.file"},...}

This seems more so the case given this output

/rpc/query?q=test&lim=5&select=path,file(path(*))
/rpc/query?q=test&lim=5&select=path,file(path!path(*))

{"hint":"By following the 'details' key, disambiguate the request by changing the url to /origin?select=relationship(*) or /origin?select=target!relationship(*)","details":[{"relationship":"file_path_fkey[path][path]","origin":"api.file","cardinality":"m2o","target":"api.path"},{"relationship":"file_path_fkey[path][path]","origin":"api.file","cardinality":"m2o","target":"api.query_result"}],"message":"More than one relationship was found for file and path"}

I'm currently using the latest docker image but i'll try and get this running off of master and let you know.

[wolfgangwalther]

I'm currently using the latest docker image but i'll try and get this running off of master and let you know.

We have our nightly builds published as docker images, too: https://hub.docker.com/layers/postgrest/postgrest/nightly-2020-12-09-14-20-ebd474a/images/sha256-e3eece662bf1a968385900d2bf199fb03ee088391bfaebd5df5351658c5300a7?context=explore

[wolfgangwalther]

I tried adding a foreign key self referencing the table but it doesn't seem to work seemingly because now api.path & api.query_result are ambiguous:

Well, yes, that's expected. You can embed both through the same column, so you need to add a hint. I suggest you add names to both your foreign keys and then use the FK name as a hint like path!fk_name(*).

[u8sand]

Awesome, the latest nightly image from dockerhub did fix the select=*,file(*) issue.

I did try a named foreign key constraint and using the hint as suggested but it still conflicted with the view (likely because that foreign key constraint was shared by both).

After several other things, I finally found something that works:

create or replace view api.query_result as
select api.path.path as id, '' as reason
from api.path;

Renaming the column was mandatory in the view, once I did so, the embedding of the path(*) worked without the hint (the self-referential foreign key was also necessary).

Thanks so much for your help and explanations!