Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-93

make number of records to get exact count for configurable

    XMLWordPrintable

Details

    • Task
    • Status: Open (View Workflow)
    • P3
    • Resolution: Unresolved
    • None
    • None
    • None
    • CP: Non-roadmap backlog
    • Core: Platform

    Description

      right now this is the count function

      CREATE OR REPLACE FUNCTION ${myuniversity}_${mymodule}.count_estimate_smart(query text) RETURNS integer AS $$
      DECLARE
        rec   record;
        rows  integer;
      BEGIN
        FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
          rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
          EXIT WHEN rows IS NOT NULL;
        END LOOP;
        IF rows < ${exactCount} THEN
          EXECUTE regexp_replace(
            query,
              '\mselect.*?from',
              'select count(*) FROM',
              'i'
           )
          INTO rec;
          rows := rec."count";
        END IF;
        RETURN rows;
      END;
      $$ LANGUAGE plpgsql VOLATILE STRICT;
      

      change this so that the 50,000 is received as a parameter to the function, something like this:
      and when creating the count_estimate_smart() clause in the postgresClient, pass in the value as a param

      CREATE OR REPLACE FUNCTION ${myuniversity}_${mymodule}.count_estimate_smart(top bigint, query text) RETURNS integer AS $$
      DECLARE
        rec   record;
        rows  integer;
      BEGIN
        FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
          rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
          EXIT WHEN rows IS NOT NULL;
        END LOOP;
        IF rows <  top THEN
          EXECUTE regexp_replace(
            query,
              '\mselect.*?from',
              'select count(*) FROM',
              'i'
           )
          INTO rec;
          rows := rec."count";
        END IF;
        RETURN rows;
      END;
      $$ LANGUAGE plpgsql VOLATILE STRICT;
      

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                Unassigned Unassigned
                shale99 shale99
                Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                  Created:
                  Updated:

                  TestRail: Runs

                    TestRail: Cases