Can results of a dynamic query feed into a CTE?

Question

Successfully generating a dynamic query based on input parameters. Now, I want the results of this dynamic query to be fed into a Common Table Expression (CTE). Is that possible without breaking this into two functions? I get an error on the line with EXECUTE qry... at the top of my CTE.

CREATE OR REPLACE FUNCTION spaidb.filter_opportunities(
    owner_id integer,
    team_id integer,
    member_id integer,
    forecast json,
    opportunity_type json,
    stage character varying,
    close_date_start timestamp without time zone,
    close_date_end timestamp without time zone,
    created_since timestamp without time zone,
    updated_since timestamp without time zone,
    unchanged_since timestamp without time zone,
    sorting json,
    current_page integer DEFAULT 1,
    page_size integer DEFAULT 100)
    RETURNS json
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
  DECLARE
    qry text := 'SELECT * from spaidb.v_opportunities';
    where_clause text := '';
    sort_clause text := '';
  BEGIN
    where_clause := where_clause || spaidb.add_clause(owner_id, '"ownerId" = $1 AND ');
    where_clause := where_clause || spaidb.add_clause(team_id, '"teamId" = $2 AND ');
    where_clause := where_clause || spaidb.add_clause(member_id, '"memberId" = $3 AND ');
    where_clause := where_clause || spaidb.add_clause(stage, 'stage = $4 AND ');
    where_clause := where_clause || spaidb.add_clause(created_since, 'created >= $5 AND ');
    where_clause := where_clause || spaidb.add_clause(updated_since, '"lastUpdated" >= $6 AND ');
    where_clause := where_clause || spaidb.add_clause(unchanged_since, '"lastUpdated" <= $7 AND ');
    where_clause := where_clause || spaidb.add_clause(close_date_start, '"closeDate" >= $8 AND ');
    where_clause := where_clause || spaidb.add_clause(close_date_end, '"closeDate" <= $9 AND ');
    where_clause := where_clause || spaidb.unpack_json_where('"opportunityType"', opportunity_type);
    where_clause := where_clause || spaidb.unpack_json_where('forecast', forecast);

    IF where_clause != '' THEN
      where_clause := ' WHERE ' || LEFT(where_clause, -5); -- add WHERE, strip last ' AND '
    END IF;

    sort_clause := spaidb.unpack_json_sort(sorting);

    qry := query || where_clause || sort_clause || ';';
    RAISE NOTICE '%', qry; -- looks good

  WITH opportunities_found AS (
    EXECUTE qry USING owner_id, team_id, member_id, stage, created_since, updated_since, unchanged_since, close_date_start, close_date_end;
  )
  SELECT json_build_object(
      'totalItems',(SELECT COUNT(*) FROM opportunities_found),
      'currentPage', current_page,
      'pageSize', page_size,
      'results', COALESCE((SELECT json_agg(l.*) FROM (SELECT opportunities_found.* FROM opportunities_found LIMIT page_size OFFSET ((current_page - 1) * page_size)) l ), '[]')
    );
*/
  END;
$BODY$;

The query that is built looks like this (depending upon which of the input parameters are null)...

SELECT * from spaidb.v_opportunities WHERE "closeDate" >= $8 AND "closeDate" <= $9 AND ("forecast" = 'Forecast' OR "forecast" = 'Upside') ORDER BY "ownerName" asc, "amount" desc;

The non-json inputs are used in the WHERE clause directly only if they are not NULL. The json inputs are arrays of values to search for a particular field or a list of columns to be sorted with their direction. That's what makes it hard to use a view in the WITH of the CTE.

Answer 1

Had to break it up into separate functions - one is SQL and the other PLPGSQL:

CREATE OR REPLACE FUNCTION spaidb.filter_opportunities(
    owner_id integer,
    team_id integer,
    member_id integer,
    forecast_input json,
    opportunity_type json,
    stage character varying,
    close_date_start timestamp without time zone,
    close_date_end timestamp without time zone,
    created_since timestamp without time zone,
    updated_since timestamp without time zone,
    unchanged_since timestamp without time zone,
    sorting json)
    RETURNS TABLE("accountName" character varying, "opportunityId" integer, "opportunityName" character varying, "ownerId" integer, "ownerName" character varying, "opportunityType" character varying, amount integer, "atRisk" boolean, discovery integer, value integer, spotlight integer, forecast character varying, "closeDate" timestamp without time zone, "stageId" integer, "stageName" character varying, "actionableInsights" json) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
  DECLARE
    qry varchar := 'SELECT * from spaidb.v_opportunities';
    where_clause varchar := '';
    sort_clause varchar := '';
  BEGIN
    where_clause := where_clause || spaidb.add_clause(owner_id, '"ownerId" = $1 AND ');
    where_clause := where_clause || spaidb.add_clause(team_id, '"teamId" = $2 AND ');
    where_clause := where_clause || spaidb.add_clause(member_id, '"memberId" = $3 AND ');
    where_clause := where_clause || spaidb.add_clause(stage, 'stage = $4 AND ');
    where_clause := where_clause || spaidb.add_clause(created_since, 'created >= $5 AND ');
    where_clause := where_clause || spaidb.add_clause(updated_since, '"lastUpdated" >= $6 AND ');
    where_clause := where_clause || spaidb.add_clause(unchanged_since, '"lastUpdated" <= $7 AND ');
    where_clause := where_clause || spaidb.add_clause(close_date_start, '"closeDate" >= $8 AND ');
    where_clause := where_clause || spaidb.add_clause(close_date_end, '"closeDate" <= $9 AND ');
    where_clause := where_clause || spaidb.unpack_json_where('"opportunityType"', opportunity_type);
    where_clause := where_clause || spaidb.unpack_json_where('forecast', forecast_input);

    IF where_clause != '' THEN
      where_clause := ' WHERE ' || LEFT(where_clause, -5); -- add WHERE, strip last ' AND '
    END IF;

    sort_clause := spaidb.unpack_json_sort(sorting);

    qry := qry || where_clause || sort_clause || ';';
    RAISE NOTICE '%', qry;
    RETURN QUERY EXECUTE qry USING owner_id, team_id, member_id, stage, created_since, updated_since, unchanged_since, close_date_start, close_date_end;
  END;
$BODY$;

CREATE OR REPLACE FUNCTION spaidb.filter_opportunities_paginated(
    owner_id integer,
    team_id integer,
    member_id integer,
    forecast_input json,
    opportunity_type json,
    stage character varying,
    close_date_start timestamp without time zone,
    close_date_end timestamp without time zone,
    created_since timestamp without time zone,
    updated_since timestamp without time zone,
    unchanged_since timestamp without time zone,
    sorting json,
    current_page integer DEFAULT 1,
    page_size integer DEFAULT 100)
    RETURNS json
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
    WITH opportunities_found AS (
    
      SELECT * from spaidb.filter_opportunities(
        owner_id,
        team_id,
        member_id,
        forecast_input,
        opportunity_type,
        stage,
        close_date_start,
        close_date_end,
        created_since,
        updated_since,
        unchanged_since,
        sorting
     )

    )
    SELECT json_build_object(
      'totalItems',(SELECT COUNT(*) FROM opportunities_found),
      'currentPage', current_page,
      'pageSize', page_size,
      'results', COALESCE((SELECT json_agg(o.*) FROM (SELECT opportunities_found.* FROM opportunities_found LIMIT page_size OFFSET ((current_page - 1) * page_size)) o ), '[]')
    );
$BODY$;