Capture values from a table and declare as a local variable in SQL Function

I’m learning how to write functions in SQL and this might be simple but can’t seem to find what I’m looking for. I have a function as follows:

CREATE OR REPLACE FUNCTION A.aggregate(r_id text)
 RETURNS VARCHAR
 LANGUAGE plpgsql
AS $function$

 
 begin
         EXECUTE('CREATE UNLOGGED TABLE F.counts AS
                  SELECT name,
                        unique_cats,
                        unique_dogs,  
                        total_cats,
                        total_dogs
                  FROM A.tb1
                  WHERE date in (select date from A.tb1) BETWEEN''' || start_date || '''AND''' || end_date ||
                  ''';''');
 END
$function$
;

I want to capture the minimum date and maximum date and use it in my execute function to filter out from my tb1 to get rows of data where the date is between the start and end date.

Answer

Modifications to your code:

-- Add to below
DECLARE
   min_date date;
   max_date date;
-- Add to below
BEGIN 
   SELECT INTO min_date, max_date min(some_date_fld), max(some_date_fld) FROM  BA.activity_dates;

... BETWEEN $1 AND $2 ...

) USING min_date, max_date;

For SELECT INTO see SELECT INTO. For USING see Dynamic.

Advertisement

Answer