PgSQL function returning table and extra data computed in process

Question

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

This needs more information. 1) Postgres version. This should always be provided. 2) What is code in `myFunc`? 3) Provide example of expected output. — Adrian Klaver, Aug 28 '22 at 16:32
I made some test, hope it can help you. https://dbfiddle.uk/?rdbms=postgres_14&fiddle=51bd62d8e9decf8fc719b03db36f02b8 — jian, Aug 28 '22 at 16:55

score 1 · Accepted Answer · answered Aug 29 '22 at 07:24

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%'.

PgSQL function returning table and extra data computed in process

1 Answers1