sql server - SQL or PL/pgSQL for the following query to have a better performance

Question

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;

question from:https://stackoverflow.com/questions/65835806/sql-or-pl-pgsql-for-the-following-query-to-have-a-better-performance

Answer 1

I ended up with the following functions:

I made GetContainsSafeText a SQL because it does not need an execution plan to be ready.

CREATE OR REPLACE FUNCTION GetContainsSafeText(p_text char varying(250))
RETURNS char varying(250)
AS $$
     select trim(regexp_replace(coalesce(nullif(coalesce(p_text, ''), ''), '""'), 's+', ' ', 'g'));    
$$ LANGUAGE SQL;

And SearchGroupQuery a PL/pgSQL because the execution plan seems complex (At least to me). So it is nice to have it ready the first time and use it so on.

CREATE OR REPLACE FUNCTION SearchGroupQuery (
    p_skip int, 
    p_count int,
    p_text char varying(250),
    p_from timestamp(6),
    p_location uuid,
    p_category uuid,
    p_geoLocation geography)
RETURNS setof "Groups" AS $$
BEGIN

    p_text := GetContainsSafeText(p_text);
 return query --<< this was missing
    with recursive LocationTree
    as
    (
        select "Id", "ParentId" from "Locations" where p_location is not null and "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  p_count offset p_skip ;

    return;
END;
$$ LANGUAGE plpgsql;

Also, please inform me if I did it wrong or did it with the wrong resean.