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:
x
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.
Advertisement
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.