How to return this query in supabase function-CodePudding

I have a table called helper which have columns id(text), title(varchar), tags(varchar), author_name(varchar), profile_pic(varchar), created_at(timestampz)

I made a supabase function:

create or replace function hello(si text)
returns setof ________ as $$
  begin
    return query select *, 1 as "priority"
      from helper
      where to_tsvector(title) @@ to_tsquery(si)
      union
      select *, 0 as "priority"
      from helper
      where to_tsvector(tags) @@ to_tsquery(si)
      order by "priority" desc;
  end;
$$ language plpgsql;

What do i have to put at underlined position to get the appropriate result. If I put helper, and call it in the supabase sql editor then I would get an error:

Failed to run sql query: structure of query does not match function result type

What I want to achieve with all of this?

I want to make a searching functionality, where I want to search 'searched text' in the 'title' column first, and then in 'tags' column. After than I want to order the result in such a manner where matched results with title comes first and matched results with tag comes later.

CodePudding user response：

You're not returning records that would fit into helper because you're adding the "priority" column, hence the structure mismatch exception. Instead of setof helper you'll have to use an explicit list of columns, with the new one added. As a bonus, you can easily switch to plain language sql to gain some performance:

create or replace function hello(si text)
returns table (
    id text, 
    title varchar,
    tags varchar,
    author_name varchar,
    profile_pic varchar,
    created_at timestampz,
    priority int) 
language sql as $$
    select *, 1 as "priority"
    from helper
    where to_tsvector(title) @@ to_tsquery(si)
    union
    select *, 0 as "priority"
    from helper
    where to_tsvector(tags) @@ to_tsquery(si)
    order by "priority" desc;
$$;

It's tempting to use returns table (like helper, priority int) that would normally work for a create table where you wish to create an identical one, just adding one column, but in this case it later results in an error on calls to a function defined that way:

ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns text instead of helper at column 1.

Workarounds using an intermediate table or view don't make it dynamic the way returns table (like helper) would (making the function result structure follow the table structure if it changes in the future), so it's not worth it. Online examples