Created
September 30, 2022 08:58
-
-
Save vbilopav/c563b79352acdbee4aea0cbee82031b7 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
); | |
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 inner join companies c on t.id = c.id | |
limit _take offset _skip | |
) sub | |
) | |
); | |
end | |
$$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment