Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


-- setup
-- ASSUMED routes table definition
create table routes(flight_num     integer
                   ,distance_miles integer
                   ,from_city      text
                   ,to_city        text
                   ,flight_time    interval
                   );  

-- populate it 
insert into routes
   values (1111,350,'City1','City2',  '01:00'::interval)
        , (1122,2000,'City1','City3', '05:30'::interval)
        , (1133,18000,'City1','City4','17:25'::interval);

select * 
  from routes;

--obereve id 1122

CREATE TABLE



INSERT 0 3




flight_num
distance_miles
from_city
to_city
flight_time


1111
350
City1
City2
01:00:00

1122
2000
City1
City3
05:30:00

1133
18000
City1
City4
17:25:00



SELECT 3


-- create UDT for routes + category
create type routes_category as (flight_num     integer
                               ,distance_miles integer
                               ,from_city      text
                               ,to_city        text
                               ,flight_time    interval
                               ,category       text
                               );

CREATE TYPE


-- create Procedure to return the UDT
create or replace procedure distances(in  flight_num1 integer
                                     ,out flight_dtl  routes_category
                                     )
language 'plpgsql'
as $body$
begin
    select 
        *,
        case
            when distance_miles >= 0 and distance_miles <= 2000    then 'short distance travel (SDT)'
            when distance_miles >= 2000 and distance_miles <= 6500 then 'Intermediate distance travel (IDT)'
            when distance_miles > 6500                             then 'Long distance travel (LDT)'
        end as category
    into  flight_dtl 
    from
        routes 
    where 
        flight_num = flight_num1;
end;
$body$; 

CREATE PROCEDURE


-- run Procedure
do $$
declare
    flight_detail routes_category;      -- Must be declared in calling routine.
begin 
    call distances(1111, flight_detail);
    raise notice 'Flight % is %',flight_detail.flight_num,flight_detail.category;

    call distances(1122, flight_detail);
    raise notice 'Flight % is %',flight_detail.flight_num,flight_detail.category;
end; 
$$;

DO


--- Define function to return routes + category 
create or replace function distances_fun(in  flight_num1 integer)
  returns  table(flight_num     integer
                ,distance_miles integer
                ,from_city      text
                ,to_city        text
                ,flight_time    interval
                ,category       text
                )
  language sql
as $body$
    select 
        *,
        case
            when distance_miles >= 0 and distance_miles <= 2000    then 'short distance travel (SDT)'
            when distance_miles >= 2000 and distance_miles <= 6500 then 'Intermediate distance travel (IDT)'
            when distance_miles > 6500                             then 'Long distance travel (LDT)'
        end as category
    from
        routes 
    where 
        flight_num = flight_num1;
$body$; 

CREATE FUNCTION


-- select form Function
select * 
  from distances_fun(1133);


flight_num
distance_miles
from_city
to_city
flight_time
category


1133
18000
City1
City4
17:25:00
Long distance travel (LDT)



SELECT 1
flight_num	distance_miles	from_city	to_city	flight_time
1111	350	City1	City2	01:00:00
1122	2000	City1	City3	05:30:00
1133	18000	City1	City4	17:25:00