search_companies.sql

create or replace function companies.search_companies(
    _search varchar, 
    _skip integer, 
    _take integer
) 
returns json
language plpgsql 
as $$
declare
    _count bigint;
begin    
    if _search is not null then
        _search = '%' || lower(_search) || '%';
    end if;
    
    create temp table _tmp on commit drop as
    select 
        c.id
    from 
        companies c
    where (
        _search is null or name_normalized like _search
    )
    order by name_normalized asc;
    
    get diagnostics _count = row_count;
    
    return json_build_object(
        'count', _count,
        'page', (
            select json_agg(sub)
            from ( 
                select c.id, name, web, linkedin, company_line, about
                from 
                    _tmp t join companies using (id)
                limit _take offset _skip                
            ) sub
        )

    );
end
$$;