Workaround for FOUND_ROW equivalent in postgreSQL stored procedure

Question

I have a mysql query shown below. I need to change it to Postgres. While doing so, I am getting error at FOUND_ROW(), there is no such function in Postgres. Could anyone let me know how to handle the function equivalent for the code below?

BEGIN
    /*declare temps varchar(2000);*/
    declare tableJoin varchar(2000) default '';
    declare filter varchar(2000) default '';
    declare whereFilters varchar(2000) default '';
    /*set temps = '3';*/
    set @q = '';
    set @join = INSTR(SUBSTRING(filters, 1, INSTR(filters, "=") - 1), ".");
    if @join > 0 then
        /*number of filters*/
        set @count = countCharsInSentence(filters, ',');
        if (@count = 0) then set @count = 1; end if;

        REPEAT
            /*return the filter with a table name*/
            set filter = SPLIT_STR(filters, ',', @count);
            set whereFilters = concat(whereFilters, ',', CONCAT(UCASE(LEFT(filter, 1)), substring(filter, 2)));
            /*return the table name*/
            set @filterTable = SPLIT_STR(filter, '.', 1);

            if (@filterTable != '') then
                set @filterTable = CONCAT(UCASE(LEFT(@filterTable, 1)), substring(@filterTable, 2));
                set tableJoin = concat(tableJoin, " ", tableName, " join ", @filterTable, " on(", tableName, ".id = ",
                                       @filterTable, ".idRef) ");
            end if;
            /*if(tableJoin!='') then set temps = tableJoin; end if;*/
            set @count = @count - 1;
        UNTIL @count <= 0
            END REPEAT;

        set filters = substring(whereFilters, 2);

        if fields is not null and fields != "" then
            set @q = concat("select id, ", fields, " from ", tableJoin, " where ");
        else
            set @q = concat("select ", tableName, ".* from ", tableJoin, " where ");
        end if;

    else
        /*Define the list of column to be retrieved and set the table name*/
        if fields is not null and fields != "" then
            set @q = concat("select id, ", fields, " from ", tableName, " where ");
        else
            set @q = concat("select * from ", tableName, " where ");
        end if;
    end if;

    set @in = INSTR(filters, " IN (");
    /*Define the where condition to filter the results*/
    if @in > 0 then
        set @where = filters;
    elseif filters is not null and filters != "" then
        set @where = concat(replace(filters, "=", "='"), "'");
        /*set @where = replace(@where, ".", "`.`");*/
        set @where = concat(replace(@where, ",", "' and "));
    else
        set @where = '1=1';
    end if;
    /*set @t = temps;*/
    /*Prepare the query*/
    set @q = concat(@q, @where);

    /*Avoid SQL Injection*/
    set @q = replace(@q, ";", "&#059;");
    /*Execute the query*/
    PREPARE stmt FROM @q;
    execute stmt;
    deallocate prepare stmt;

END ;;

I am getting error in Postgres in FOUND_ROW as there is no equivalent in pgSQL for it. Can anyone let me know the best alternative that I can put in place of FOUND_ROW in my stored procedure?

There is no `found_row()` in the code you posted, so we cannot see the context. And there is no such function in MySQL either. There is a function [`found_rows()`](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows) though. But it's deprecated. MySQL's documentation suggests to replace it with a query getting `count(*)`. That would also work in Postgres. But there are *a lot* of other things in that code that won't work that way in Postgres... — sticky bit, Dec 05 '21 at 06:47
Related: https://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied/8242764#8242764 — Erwin Brandstetter, Dec 05 '21 at 13:55
Here [Result Status](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS) and the `FOUND` variable? Since you have not actually shown how `found_rows()` is being used in your code it is difficult to get any more specific. — Adrian Klaver, Dec 05 '21 at 16:16

score 3 · Answer 1 · edited Dec 05 '21 at 13:54

3

There is no equivalent for MySQL's found_rows in Postgres. The LIMIT clause stops query execution immediately, so the number of result rows without LIMIT remains unknown.

To get the number of returned rows for an embedded query, use GET DIAGNOSTICS and the ROW_COUNT item. Or just use COUNT(*).

edited Dec 05 '21 at 13:54

Erwin Brandstetter

605,456
145
1,078
1,228

answered Dec 05 '21 at 07:59

Pavel Stehule

42,331
5
91
94

Workaround for FOUND_ROW equivalent in postgreSQL stored procedure

1 Answers1