I am migrating from SQL Server to PostgreSQL and I have the following stored procedure and function in SQL Server.
ALTER FUNCTION [dbo].[GetContainsSafeText] (@text nvarchar(250))
RETURNS nvarchar(250)
AS
BEGIN
SET @text = ISNULL(NULLIF(ISNULL(@text, ''), ''), '""');
SET @text = TRIM(@text);
WHILE (CHARINDEX(' ', @text) > 0)
SET @text = REPLACE(@text, ' ', ' ');
SET @text = REPLACE(@text, ' ', ' AND ');
RETURN @text;
END
ALTER PROCEDURE [dbo].[SearchGroupQuery]
@skip int,
@count int,
@text nvarchar(250),
@from datetime2,
@location uniqueidentifier,
@category uniqueidentifier,
@geoLocation geography
AS
BEGIN
SET NOCOUNT ON;
SET @text = dbo.GetContainsSafeText(@text);
WITH LocationTree AS
(
SELECT Id, ParentId
FROM Locations
WHERE Id = @location
UNION ALL
SELECT Locations.Id, Locations.ParentId
FROM LocationTree
JOIN Locations ON LocationTree.Id = Locations.ParentId
)
SELECT *
FROM [groups] g
WHERE (@text = '""' OR
CONTAINS([Description], @text) OR
CONTAINS([Title], @text) OR
Id IN (SELECT GroupId
FROM [Events]
WHERE [Time] >= @from
AND (CONTAINS([Description], @text) OR
CONTAINS([Title], @text))
))
AND (@location IS NULL OR LocationId IN (SELECT Id FROM LocationTree))
AND (@category IS NULL OR Id IN (SELECT GroupId FROM [GroupCategories] cg
WHERE cg.CategoryId = @category))
ORDER BY
Title
OFFSET @skip ROWS FETCH NEXT @count ROWS ONLY;
END
I have migrated them to the following but I faced with the control reached end of function without RETURN. I found this question but the accepted answer was not helpful for me. It just made me wondered which one is better SQL or PL/pgSQL. I found the accepted answer here interesting. If I understand it correctly, I cannot use SQL but the question is how can I make the following better? Also, adding return query did not solve my issue.
CREATE OR REPLACE FUNCTION GetContainsSafeText(p_text char varying(250))
RETURNS char varying(250)
AS $$
BEGIN
p_text := coalesce(nullif(coalesce(p_text, ''), ''), '""');
p_text := trim(p_text);
while(POSITION(' ' IN p_text) > 0) loop
p_text := replace(p_text, ' ', ' ');
END Loop;
p_text := replace(p_text, ' ', ' & ');
RETURN p_text;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION SearchGroupQuery (
p_skip int,
p_count int,
p_text char varying(250),
p_from timestamp(6),
p_location char varying(36),
p_category char varying(36),
p_geoLocation geography)
RETURNS setof "Groups" AS $$
BEGIN
p_text := GetContainsSafeText(p_text);
return query --<< this was missing the first time
with recursive LocationTree
as
(
select "Id", "ParentId" from "Locations" where "Id" = p_location
union all
select "Locations"."Id", "Locations"."ParentId" from LocationTree t join "Locations" on t."Id" = "Locations"."ParentId"
)
select
*
from "Groups" g
where
(
p_text = '""""' or
to_tsvector('simple', "Title" || ' ' || "Description") @@ to_tsquery(p_text) or
Id in (
select "GroupId"
from "Events"
where "Time" >= p_from and
to_tsvector('simple', "Title" || ' ' || "Description") @@ to_tsquery(p_text)
)
) and
(
p_location is null or
"LocationId" in (select "Id" from "LocationTree")
) and
(
p_category is null or
"Id" in (
select "GroupId"
from "GroupCategories" cg
where cg."CategoryId" = p_category
)
)
order by "Title"
LIMIT v_count offset p_skip ;
END;
$$ LANGUAGE plpgsql;
