PostgreSQL temp function for minimum date

Question

I am trying to create a temp function to avoid duplicating blocks of code in the script. I think by creating a temp function in PostgreSQL I will be able to keep the script succinct but I am unfamiliar with functions on PostgreSQL

I would like to get the minimum date value by a specific group, i.e. I have the following SQL script

select
  my_tale.some_value_id
  , min(my_tale.datetime::date)
      filter(
        where
          my_tale.some_value_id_2 in (1, 2)
      ) as firs_date

from
  my_tale
group by
  1

I would like to swap out some_value_id_2 in a function so I don't have to reuse code, i.e. I don't want to do this:

select
  my_tale.some_value_id
  , min(my_tale.datetime::date)
      filter(
        where
          my_tale.some_value_id_2 in (1, 2)
      ) as firs_date_1
  , min(my_tale.datetime::date)
      filter(
        where
          my_tale.some_value_id_2 in (3, 4)
      ) as firs_date_2
  , min(my_tale.datetime::date)
      filter(
        where
          my_tale.some_value_id_2 in (5, 6)
      ) as firs_date_3

from
  my_tale
group by
  1

Ideally I would like to have something like this:

select
  my_tale.some_value_id
  , temp_function(1,2) as firs_date_1
  , temp_function(3,4) as firs_date_2
  , temp_function(5,6) as firs_date_3
from
  my_tale
group by
  1

I have tried playing around with the function below, but can't get it to work:

create function pg_temp.get_first_date(id_1 int, id_2 int)
  returns date
  language sql
  as $function$
    min(my_tale.datetime::date)
      filter(
        where my_tale.some_value_id_2 in ($id_1, $id_2)
      );
    $function$
;

select
  my_tale.some_value_id
  , pg_temp.get_first_date(1,2) as firs_date_1
from
  my_tale
group by
  1

Any advice would be appreciated, thanks in advance

user_15603477 · Answer

sql fiddle reference: https://dba.stackexchange.com/questions/300507/what-does-begin-atomic-end-mean-in-a-postgresql-sql-function-procedure

begin;
create temp table mytable(mytableid bigint,some_value_id numeric, some_value_id_2 numeric, datetime date);
insert into  mytable values (1,1,1,'2022-01-12');
insert into  mytable values (1,1,2,'2022-03-01');
insert into  mytable values (1,1,3,'2022-01-01');
insert into  mytable values (1,1,1,'2022-01-11');
commit;



create function
    pg_temp.get_first_date(id_1 int, id_2 int)
    returns  date
language sql
BEGIN ATOMIC
select  min(datetime)
    filter(where mytable.some_value_id_2 in ($1, $2)) from mytable;
end;

PostgreSQL temp function for minimum date

1 Answers