PgSQL function returning table and extra data computed in process-CodePudding

In PgSQL I make huge select, and then I want count it's size and apply some extra filters. execute it twice sound dumm, so I wrapped it in function and then "cache" it and return union of filtered table and extra row at the end where in "id" column store size

with q as (select * from myFunc())
select * from q
where q.distance < 400
union all
select count(*) as id, null,null,null
from q

but it also doesn't look like proper solution...

and so the question: is in pg something like "generator function" or any other stuff that can properly solve this ?

postgreSQL 13

myFunc aka "selectItemsByRootTag"

CREATE OR REPLACE FUNCTION selectItemsByRootTag(
    in tag_name VARCHAR(50)
)
RETURNS table(
    id BIGINT,
    name VARCHAR(50),
    description TEXT,
    /*info JSON,*/
    distance INTEGER
)
AS $$
BEGIN
RETURN QUERY(
    WITH RECURSIVE prod AS (
        SELECT
            tags.name, tags.id, tags.parent_tags
        FROM      
            tags
        WHERE tags.name = (tags_name)
        UNION
        SELECT c.name, c.id , c.parent_tags
        FROM 
            tags as c
            INNER JOIN prod as p
                ON c.parent_tags = p.id
    )
    SELECT
        points.id,
        points.name,
        points.description,
        /*points.info,*/
        points.distance
    from points
    left join tags on points.tag_id = tags.id
    where tags.name in (select prod.name from prod)
);
END;
$$ LANGUAGE plpgsql;

as a result i want see maybe set of 2 table or generator function that yield some intermediate result not shure how exacltly it should look

CodePudding user response：

demo

CREATE OR REPLACE FUNCTION pg_temp.selectitemsbyroottag(tag_name text, _distance numeric)
 RETURNS TABLE(id bigint, name text, description text, distance numeric, count bigint)
 LANGUAGE plpgsql
AS $function$
DECLARE _sql text;
BEGIN
_sql := $p1$WITH RECURSIVE prod AS (
            SELECT
                tags.name, tags.id, tags.parent_tags
            FROM
                tags
            WHERE tags.name ilike '%$p1$ || tag_name || $p2$%'
            UNION
            SELECT c.name, c.id , c.parent_tags
            FROM
                tags as c
                INNER JOIN prod as p
                    ON c.parent_tags = p.id
        )
        SELECT
            points.id,
            points.name,
            points.description,
            points.distance,
            count(*) over ()
        from points
        left join tags on points.tag_id = tags.id
        where tags.name in (select prod.name from prod)
        and points.distance > $p2$ || _distance
    ;
raise notice '_sql: %', _sql;

return query execute _sql;
END;
$function$

You can call it throug following way

select * from pg_temp.selectItemsByRootTag('test',20);
select * from pg_temp.selectItemsByRootTag('test_8',20) with ORDINALITY;

The 1 way to call the function, will have a row of total count total number of rows. Second way call have number of rows plus a serial incremental number.

I also make where q.distance < 400 into function input argument. selectItemsByRootTag('test',20); means that q.distance > 20 and tags.name ilike '%test%'.