Goal: tracking flight prices over time to get the best/lowest price
Did some research with Claude and best api seems to be the Google Flight API offered by SerpApi.com.
For the development of this feature, Claude Code was used for most part. Even though Claude did not get everything correct, it helped realizing this feature in just an afternoon. Only the debugging of why running it from Home Assistant did not work took some more time. Especially since ChatGPT came up with a lot of false reasons and solutions (because I had to wait 3 hours for new Claude tokens).
Retrieving and displaying data all works. Only still not able to automate the daily data retrieval due to problems with HA (see Problem running the function in Home Assistant. So perhaps I fall back to using the Bash script with cron instead.
- Query Rest api for flights
- Store result in a database
- Visualize results on Home Assistant dashboard
Create an account on serpapi.com and get an api-key.
Example: looking for flights from Madrid to San Jose (Costa Rica) excluding all American airlines and American international airports as stop-over.
curl -v https://serpapi.com/search?engine=google_flights&departure_id=MAD&arrival_id=SJO&outbound_date=2026-07-13&return_date=2026-08-17&type=1&adults=4&travel_class=1&stops=2&exclude_airlines=AA,UA,DL,AS,B6,HA&exclude_conns=ATL,LAX,ORD,DFW,DEN,JFK,SFO,SEA,LAS,MCO,EWR,CLT,PHX,IAH,MIA,BOS,FLL,MSP,DTW,PHL,LGA,BWI,IAD,DCA,PDX,SLC,SAN,TPA,BNA,AUS,RDU,MSY,HNL,ANC&api_key=YOUR_API_KEYA simple single table to store all results.
CREATE TABLE flight_prices (
id SERIAL PRIMARY KEY,
departure_airport VARCHAR(3) NOT NULL,
arrival_airport VARCHAR(3) NOT NULL,
outbound_date DATE NOT NULL,
return_date DATE,
total_price NUMERIC(10,2) NOT NULL,
price_per_person NUMERIC(10,2),
num_passengers INTEGER,
is_best_price BOOLEAN DEFAULT FALSE,
airlines VARCHAR(255),
layover_airports VARCHAR(100),
num_stops INTEGER,
total_duration_hours NUMERIC(5,1),
has_overnight_layover BOOLEAN DEFAULT FALSE,
often_delayed BOOLEAN DEFAULT FALSE,
search_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
notes TEXT
);First try was to use a Bash script to perform the Rest call, parse the results and store them in a database.
#!/bin/bash
#############################################
# Flight Search & Database Insert Script
# Searches for flights via SerpApi and stores results in PostgreSQL
#############################################
# Database Configuration - SET THESE VALUES
DB_HOST="YOUR_HOST"
DB_PORT="5432"
DB_NAME="flightprices"
DB_USER="flightchecker"
DB_PASSWORD="YOUR_PASSWORD"
# SerpApi Configuration
SERPAPI_KEY="YOUR_API_KEY"
# Flight Search Parameters
DEPARTURE="MAD"
ARRIVAL="SJO"
OUTBOUND_DATE="2026-07-13"
RETURN_DATE="2026-08-17"
ADULTS=4
TRAVEL_CLASS=1 # 1=Economy, 2=Premium Economy, 3=Business, 4=First
MAX_STOPS=2 # 2 = 1 stop or fewer
# Airlines to exclude (comma-separated)
EXCLUDE_AIRLINES="AA,UA,DL,AS,B6,HA"
# Airports to exclude as connections (comma-separated)
EXCLUDE_CONNS="ATL,LAX,ORD,DFW,DEN,JFK,SFO,SEA,LAS,MCO,EWR,CLT,PHX,IAH,MIA,BOS,FLL,MSP,DTW,PHL,LGA,BWI,IAD,DCA,PDX,SLC,SAN,TPA,BNA,AUS,RDU,MSY,HNL,ANC"
# Temporary file for API response
TEMP_FILE="/tmp/flight_search_$$.json"
#############################################
# Functions
#############################################
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}
error_exit() {
log "ERROR: $1"
rm -f "$TEMP_FILE"
exit 1
}
# Function to escape single quotes for SQL
escape_sql() {
echo "$1" | sed "s/'/''/g"
}
# Function to create flight description
create_flight_description() {
local flight_index=$1
local is_best=$2
if [ "$is_best" = "true" ]; then
local json_path=".best_flights[$flight_index]"
else
local json_path=".other_flights[$flight_index]"
fi
# Get number of flight legs
local num_legs=$(jq "${json_path}.flights | length" "$TEMP_FILE")
# Build description
local description=""
for leg in $(seq 0 $((num_legs - 1))); do
local dep_airport=$(jq -r "${json_path}.flights[$leg].departure_airport.name" "$TEMP_FILE")
local dep_code=$(jq -r "${json_path}.flights[$leg].departure_airport.id" "$TEMP_FILE")
local arr_airport=$(jq -r "${json_path}.flights[$leg].arrival_airport.name" "$TEMP_FILE")
local arr_code=$(jq -r "${json_path}.flights[$leg].arrival_airport.id" "$TEMP_FILE")
local airline=$(jq -r "${json_path}.flights[$leg].airline" "$TEMP_FILE")
local flight_num=$(jq -r "${json_path}.flights[$leg].flight_number" "$TEMP_FILE")
if [ $leg -eq 0 ]; then
description="${description}${airline} ${flight_num} from ${dep_airport} (${dep_code}) to ${arr_airport} (${arr_code})"
else
description="${description}, then ${airline} ${flight_num} to ${arr_airport} (${arr_code})"
fi
done
# Add layover information if exists
local num_layovers=$(jq "${json_path}.layovers | length" "$TEMP_FILE")
if [ $num_layovers -gt 0 ]; then
description="${description}. Layover"
if [ $num_layovers -gt 1 ]; then
description="${description}s"
fi
description="${description} at: "
for layover in $(seq 0 $((num_layovers - 1))); do
local layover_name=$(jq -r "${json_path}.layovers[$layover].name" "$TEMP_FILE")
local layover_code=$(jq -r "${json_path}.layovers[$layover].id" "$TEMP_FILE")
local layover_duration=$(jq -r "${json_path}.layovers[$layover].duration" "$TEMP_FILE")
local layover_hours=$(echo "scale=1; $layover_duration / 60" | bc)
if [ $layover -gt 0 ]; then
description="${description}, "
fi
description="${description}${layover_name} (${layover_code}) ${layover_hours}h"
done
fi
description="${description}."
# Escape for SQL
echo "$(escape_sql "$description")"
}
#############################################
# Main Script
#############################################
log "Starting flight search..."
# Build API URL
API_URL="https://serpapi.com/search?engine=google_flights"
API_URL="${API_URL}&departure_id=${DEPARTURE}"
API_URL="${API_URL}&arrival_id=${ARRIVAL}"
API_URL="${API_URL}&outbound_date=${OUTBOUND_DATE}"
API_URL="${API_URL}&return_date=${RETURN_DATE}"
API_URL="${API_URL}&type=1"
API_URL="${API_URL}&adults=${ADULTS}"
API_URL="${API_URL}&travel_class=${TRAVEL_CLASS}"
API_URL="${API_URL}&stops=${MAX_STOPS}"
API_URL="${API_URL}&exclude_airlines=${EXCLUDE_AIRLINES}"
API_URL="${API_URL}&exclude_conns=${EXCLUDE_CONNS}"
API_URL="${API_URL}&api_key=${SERPAPI_KEY}"
log "Fetching flight data from SerpApi using url $API_URL..."
if ! curl -s "$API_URL" -o "$TEMP_FILE"; then
error_exit "Failed to fetch data from SerpApi"
fi
# Check if file exists and has content
if [ ! -s "$TEMP_FILE" ]; then
error_exit "API response is empty"
fi
# Check if response is valid JSON
if ! jq empty "$TEMP_FILE" 2>/dev/null; then
error_exit "Invalid JSON response from API"
fi
log "Processing flight data..."
# Extract and process best_flights
BEST_FLIGHTS_COUNT=$(jq '.best_flights | length' "$TEMP_FILE")
log "Found $BEST_FLIGHTS_COUNT best flight(s)"
# Process best flights
for i in $(seq 0 $((BEST_FLIGHTS_COUNT - 1))); do
log "Processing best flight #$((i + 1))..."
# Extract flight data
PRICE=$(jq -r ".best_flights[$i].price" "$TEMP_FILE")
TOTAL_DURATION=$(jq -r ".best_flights[$i].total_duration" "$TEMP_FILE")
# Calculate duration in hours
DURATION_HOURS=$(echo "scale=1; $TOTAL_DURATION / 60" | bc)
# Calculate price per person
PRICE_PER_PERSON=$(echo "scale=2; $PRICE / $ADULTS" | bc)
# Get airlines (combine all unique airlines from flights)
AIRLINES=$(jq -r ".best_flights[$i].flights[].airline" "$TEMP_FILE" | sort -u | tr '\n' ' + ' | sed 's/ + $//')
AIRLINES=$(escape_sql "$AIRLINES")
# Get layover airports
LAYOVER_AIRPORTS=$(jq -r ".best_flights[$i].layovers[]?.id" "$TEMP_FILE" | tr '\n' ',' | sed 's/,$//')
NUM_STOPS=$(jq ".best_flights[$i].layovers | length" "$TEMP_FILE")
# Check for overnight layover
HAS_OVERNIGHT=$(jq -r ".best_flights[$i].layovers[]?.overnight // false" "$TEMP_FILE" | grep -q "true" && echo "true" || echo "false")
# Check if any flight is often delayed
OFTEN_DELAYED=$(jq -r ".best_flights[$i].flights[].often_delayed_by_over_30_min // false" "$TEMP_FILE" | grep -q "true" && echo "true" || echo "false")
# Create flight description
FLIGHT_DESCRIPTION=$(create_flight_description $i "true")
# Build SQL INSERT statement
SQL="INSERT INTO flight_prices (
departure_airport,
arrival_airport,
outbound_date,
return_date,
total_price,
price_per_person,
num_passengers,
is_best_price,
airlines,
layover_airports,
num_stops,
total_duration_hours,
has_overnight_layover,
often_delayed,
notes
) VALUES (
'$DEPARTURE',
'$ARRIVAL',
'$OUTBOUND_DATE',
'$RETURN_DATE',
$PRICE,
$PRICE_PER_PERSON,
$ADULTS,
true,
'$AIRLINES',
'${LAYOVER_AIRPORTS:-NULL}',
$NUM_STOPS,
$DURATION_HOURS,
$HAS_OVERNIGHT,
$OFTEN_DELAYED,
'$FLIGHT_DESCRIPTION'
);"
# Execute SQL
log "Inserting best flight into database..."
PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "$SQL" || error_exit "Failed to insert best flight"
done
# Extract and process other_flights
OTHER_FLIGHTS_COUNT=$(jq '.other_flights | length' "$TEMP_FILE")
log "Found $OTHER_FLIGHTS_COUNT other flight(s)"
# Process other flights
for i in $(seq 0 $((OTHER_FLIGHTS_COUNT - 1))); do
log "Processing other flight #$((i + 1))..."
# Extract flight data
PRICE=$(jq -r ".other_flights[$i].price" "$TEMP_FILE")
TOTAL_DURATION=$(jq -r ".other_flights[$i].total_duration" "$TEMP_FILE")
# Calculate duration in hours
DURATION_HOURS=$(echo "scale=1; $TOTAL_DURATION / 60" | bc)
# Calculate price per person
PRICE_PER_PERSON=$(echo "scale=2; $PRICE / $ADULTS" | bc)
# Get airlines
AIRLINES=$(jq -r ".other_flights[$i].flights[].airline" "$TEMP_FILE" | sort -u | tr '\n' ' + ' | sed 's/ + $//')
AIRLINES=$(escape_sql "$AIRLINES")
# Get layover airports
LAYOVER_AIRPORTS=$(jq -r ".other_flights[$i].layovers[]?.id" "$TEMP_FILE" | tr '\n' ',' | sed 's/,$//')
NUM_STOPS=$(jq ".other_flights[$i].layovers | length" "$TEMP_FILE")
# Check for overnight layover
HAS_OVERNIGHT=$(jq -r ".other_flights[$i].layovers[]?.overnight // false" "$TEMP_FILE" | grep -q "true" && echo "true" || echo "false")
# Check if any flight is often delayed
OFTEN_DELAYED=$(jq -r ".other_flights[$i].flights[].often_delayed_by_over_30_min // false" "$TEMP_FILE" | grep -q "true" && echo "true" || echo "false")
# Create flight description
FLIGHT_DESCRIPTION=$(create_flight_description $i "false")
# Build SQL INSERT statement
SQL="INSERT INTO flight_prices (
departure_airport,
arrival_airport,
outbound_date,
return_date,
total_price,
price_per_person,
num_passengers,
is_best_price,
airlines,
layover_airports,
num_stops,
total_duration_hours,
has_overnight_layover,
often_delayed,
notes
) VALUES (
'$DEPARTURE',
'$ARRIVAL',
'$OUTBOUND_DATE',
'$RETURN_DATE',
$PRICE,
$PRICE_PER_PERSON,
$ADULTS,
false,
'$AIRLINES',
'${LAYOVER_AIRPORTS:-NULL}',
$NUM_STOPS,
$DURATION_HOURS,
$HAS_OVERNIGHT,
$OFTEN_DELAYED,
'$FLIGHT_DESCRIPTION'
);"
# Execute SQL
log "Inserting other flight into database..."
PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "$SQL" || error_exit "Failed to insert other flight"
done
# Cleanup
rm -f "$TEMP_FILE"
log "Successfully inserted $((BEST_FLIGHTS_COUNT + OTHER_FLIGHTS_COUNT)) flight options into database"
log "Done!"This works fine. But since I wanted to integrate it with Home Assistant, I thought using it via a PSQL function would be simpler, so...
I'm using Proxmox to run all kinds of Linux containers (including Home Assistant). For this setup I create a new container, based on Linux Turnkey Postgres 18.1 images.
Note that even though the image version is 18, it still comes with a Postgres 15 version!!! So after starting the new container, I manually updated the container to Debian 13 'Trixie' and upgraded Postgres to version 18.
After Postgres was running, I used the 'Adminer' coming with this Turnkey image to create a new user to own the new database, and also a read-only user to be used by Home Assistant. Then I connected to the database with the new user using DBeaver.
This PSQL function allows for search for return flights on given inbound/outbound dates for give given number of adults like so:
SELECT * FROM fetch_and_save_flight_prices('MAD', 'SJO', '2026-07-14', '2026-08-18', 4);-- DROP FUNCTION public.fetch_and_save_flight_prices(varchar, varchar, date, date, int4);
CREATE OR REPLACE FUNCTION public.fetch_and_save_flight_prices(p_departure character varying, p_arrival character varying, p_outbound_date date, p_return_date date DEFAULT NULL::date, p_num_passengers integer DEFAULT 1)
RETURNS TABLE(status_code integer, flights_saved integer, message text)
LANGUAGE plpgsql
AS $function$
DECLARE
v_url TEXT;
v_response http_response;
v_json_data JSONB;
v_flight JSONB;
v_flights_saved INTEGER := 0;
v_exclude_airlines TEXT;
v_exclude_airports TEXT;
v_api_key TEXT := 'YOUR_API_KEY';
v_currency TEXT := 'EUR';
v_max_stops INTEGER := 2; -- 2 = 1 stop or fewer
v_travel_class INTEGER := 1; -- 1=Economy, 2=Premium Economy, 3=Business, 4=First
v_language TEXT := 'en';
v_type INTEGER := 1; -- 1=Round Trip, 2=One Way, 3=Multi City
v_deep_search BOOLEAN := FALSE; -- True enables deep search which might return more results
v_request_timeout INTEGER := 300000; -- 300 seconds (5min)
BEGIN
-- Get excluded airlines from exclusions table
SELECT string_agg(value, ',')
INTO v_exclude_airlines
FROM exclusions
WHERE type = 'AIRLINE';
-- Get excluded airports from exclusions table
SELECT string_agg(value, ',')
INTO v_exclude_airports
FROM exclusions
WHERE type = 'AIRPORT';
-- Construct the API URL
v_url := format(
'https://serpapi.com/search?engine=google_flights&departure_id=%s&arrival_id=%s&outbound_date=%s¤cy=%s&hl=%s&stops=%s&travel_class=%s&adults=%s&type=%s&api_key=%s',
p_departure,
p_arrival,
p_outbound_date,
v_currency,
v_language,
v_max_stops::TEXT,
v_travel_class::TEXT,
p_num_passengers::TEXT,
v_type::TEXT,
v_api_key
);
-- Add return date if provided
IF p_return_date IS NOT NULL THEN
v_url := v_url || '&return_date=' || p_return_date;
END IF;
-- Add excluded airlines if any exist
IF v_exclude_airlines IS NOT NULL THEN
v_url := v_url || '&exclude_airlines=' || v_exclude_airlines;
END IF;
-- Add excluded airports if any exist
-- IF v_exclude_airports IS NOT NULL THEN
-- v_url := v_url || '&exclude_conns="' || v_exclude_airports || '"';
-- END IF;
-- Log the URL before making the request
RAISE NOTICE 'Making HTTP request to: %', v_url;
-- Make the HTTP request
PERFORM http_set_curlopt('CURLOPT_TIMEOUT_MS', v_request_timeout::TEXT);
v_response := http_get(v_url);
-- Check if request was successful
IF v_response.status != 200 THEN
RETURN QUERY SELECT
v_response.status,
0,
'HTTP request failed: ' || v_response.status::TEXT || ' - Response: ' || v_response.content;
RETURN;
END IF;
RAISE NOTICE 'Response content: %', v_response.content;
-- Parse JSON response
v_json_data := v_response.content::JSONB;
-- Check if response contains an error
IF v_json_data ? 'error' AND v_json_data->>'error' != '' THEN
RETURN QUERY SELECT
v_response.status,
0,
'API Error: ' || (v_json_data->>'error');
RETURN;
END IF;
-- Process best flights
IF v_json_data ? 'best_flights' THEN
FOR v_flight IN SELECT * FROM jsonb_array_elements(v_json_data->'best_flights')
LOOP
INSERT INTO flight_prices (
departure_airport,
arrival_airport,
outbound_date,
return_date,
total_price,
price_per_person,
num_passengers,
is_best_price,
airlines,
layover_airports,
num_stops,
total_duration_hours,
has_overnight_layover,
often_delayed,
notes
) VALUES (
p_departure,
p_arrival,
p_outbound_date,
p_return_date,
(v_flight->>'price')::NUMERIC,
CASE
WHEN p_num_passengers > 0
THEN ((v_flight->>'price')::NUMERIC / p_num_passengers)
ELSE NULL
END,
p_num_passengers,
TRUE,
(
SELECT string_agg(DISTINCT flight->>'airline', ', ')
FROM jsonb_array_elements(v_flight->'flights') AS flight
),
(
SELECT string_agg(flight->'arrival_airport'->>'id', ',')
FROM jsonb_array_elements(v_flight->'flights') AS flight
WHERE (flight->'arrival_airport'->>'id') != p_arrival
LIMIT (jsonb_array_length(v_flight->'flights') - 1)
),
COALESCE(jsonb_array_length(v_flight->'flights'), 1) - 1,
(v_flight->>'total_duration')::NUMERIC / 60.0,
COALESCE((v_flight->>'overnight')::BOOLEAN, FALSE),
COALESCE((v_flight->>'often_delayed_by_over_30_min')::BOOLEAN, FALSE),
create_flight_description(v_flight)
);
v_flights_saved := v_flights_saved + 1;
END LOOP;
END IF;
-- Process other flights
IF v_json_data ? 'other_flights' THEN
FOR v_flight IN SELECT * FROM jsonb_array_elements(v_json_data->'other_flights')
LOOP
INSERT INTO flight_prices (
departure_airport,
arrival_airport,
outbound_date,
return_date,
total_price,
price_per_person,
num_passengers,
is_best_price,
airlines,
layover_airports,
num_stops,
total_duration_hours,
has_overnight_layover,
often_delayed,
notes
) VALUES (
p_departure,
p_arrival,
p_outbound_date,
p_return_date,
(v_flight->>'price')::NUMERIC,
CASE
WHEN p_num_passengers > 0
THEN ((v_flight->>'price')::NUMERIC / p_num_passengers)
ELSE NULL
END,
p_num_passengers,
FALSE,
(
SELECT string_agg(DISTINCT flight->>'airline', ', ')
FROM jsonb_array_elements(v_flight->'flights') AS flight
),
(
SELECT string_agg(flight->'arrival_airport'->>'id', ',')
FROM jsonb_array_elements(v_flight->'flights') AS flight
WHERE (flight->'arrival_airport'->>'id') != p_arrival
LIMIT (jsonb_array_length(v_flight->'flights') - 1)
),
COALESCE(jsonb_array_length(v_flight->'flights'), 1) - 1,
(v_flight->>'total_duration')::NUMERIC / 60.0,
COALESCE((v_flight->>'overnight')::BOOLEAN, FALSE),
COALESCE((v_flight->>'often_delayed_by_over_30_min')::BOOLEAN, FALSE),
create_flight_description(v_flight)
);
v_flights_saved := v_flights_saved + 1;
END LOOP;
END IF;
RETURN QUERY SELECT
v_response.status,
v_flights_saved,
'Successfully saved ' || v_flights_saved || ' flights';
EXCEPTION WHEN OTHERS THEN
RETURN QUERY SELECT
5000,
0,
'Error: ' || SQLERRM;
END;
$function$
;Indexes
-- Indexes for price analysis
CREATE INDEX idx_route ON flight_prices(departure_airport, arrival_airport);
CREATE INDEX idx_dates ON flight_prices(outbound_date, return_date);
CREATE INDEX idx_price ON flight_prices(total_price);
CREATE INDEX idx_search_date ON flight_prices(search_date);An auxilary exclusions table to exclude airlines and layover airports when querying the Rest API for flights.
Note that these tables are ONLY used by the PSQL function. Not by the Bash script.
These exclusions exclude all American airlines and international airports for layover stops.
CREATE TABLE exclusions (
type TEXT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (type, value)
);
drop table exclusions
INSERT INTO exclusions (type, value) VALUES
('AIRLINE', 'AA'),
('AIRLINE', 'UA'),
('AIRLINE', 'DL'),
('AIRLINE', 'AS'),
('AIRLINE', 'B6'),
('AIRLINE', 'HA');
INSERT INTO exclusions (type, value) VALUES
('AIRPORT', 'ATL'),
('AIRPORT', 'LAX'),
('AIRPORT', 'ORD'),
('AIRPORT', 'DFW'),
('AIRPORT', 'DEN'),
('AIRPORT', 'JFK'),
('AIRPORT', 'SFO'),
('AIRPORT', 'SEA'),
('AIRPORT', 'LAS'),
('AIRPORT', 'MCO'),
('AIRPORT', 'EWR'),
('AIRPORT', 'CLT'),
('AIRPORT', 'PHX'),
('AIRPORT', 'IAH'),
('AIRPORT', 'MIA'),
('AIRPORT', 'BOS'),
('AIRPORT', 'FLL'),
('AIRPORT', 'MSP'),
('AIRPORT', 'DTW'),
('AIRPORT', 'PHL'),
('AIRPORT', 'LGA'),
('AIRPORT', 'BWI'),
('AIRPORT', 'IAD'),
('AIRPORT', 'DCA'),
('AIRPORT', 'PDX'),
('AIRPORT', 'SLC'),
('AIRPORT', 'SAN'),
('AIRPORT', 'TPA'),
('AIRPORT', 'BNA'),
('AIRPORT', 'AUS'),
('AIRPORT', 'RDU'),
('AIRPORT', 'MSY'),
('AIRPORT', 'HNL'),
('AIRPORT', 'ANC');To create a nice description of the flight, which is stored in the notes column,
this PSQL function is used.
-- DROP FUNCTION public.create_flight_description(jsonb);
CREATE OR REPLACE FUNCTION public.create_flight_description(p_flight jsonb)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
v_description TEXT := '';
v_num_legs INTEGER;
v_leg JSONB;
v_leg_index INTEGER := 0;
v_dep_airport TEXT;
v_dep_code TEXT;
v_arr_airport TEXT;
v_arr_code TEXT;
v_airline TEXT;
v_flight_num TEXT;
v_num_layovers INTEGER;
v_layover JSONB;
v_layover_index INTEGER := 0;
v_layover_name TEXT;
v_layover_code TEXT;
v_layover_duration NUMERIC;
v_layover_hours NUMERIC;
BEGIN
-- Get number of flight legs
v_num_legs := jsonb_array_length(p_flight->'flights');
-- Build description for each leg
FOR v_leg IN SELECT * FROM jsonb_array_elements(p_flight->'flights')
LOOP
v_dep_airport := v_leg->'departure_airport'->>'name';
v_dep_code := v_leg->'departure_airport'->>'id';
v_arr_airport := v_leg->'arrival_airport'->>'name';
v_arr_code := v_leg->'arrival_airport'->>'id';
v_airline := v_leg->>'airline';
v_flight_num := v_leg->>'flight_number';
IF v_leg_index = 0 THEN
v_description := v_description || v_airline || ' ' || v_flight_num ||
' from ' || v_dep_airport || ' (' || v_dep_code || ')' ||
' to ' || v_arr_airport || ' (' || v_arr_code || ')';
ELSE
v_description := v_description || ', then ' || v_airline || ' ' || v_flight_num ||
' to ' || v_arr_airport || ' (' || v_arr_code || ')';
END IF;
v_leg_index := v_leg_index + 1;
END LOOP;
-- Add layover information if exists
IF p_flight ? 'layovers' THEN
v_num_layovers := jsonb_array_length(p_flight->'layovers');
IF v_num_layovers > 0 THEN
v_description := v_description || '. Layover';
IF v_num_layovers > 1 THEN
v_description := v_description || 's';
END IF;
v_description := v_description || ' at: ';
FOR v_layover IN SELECT * FROM jsonb_array_elements(p_flight->'layovers')
LOOP
v_layover_name := v_layover->>'name';
v_layover_code := v_layover->>'id';
v_layover_duration := (v_layover->>'duration')::NUMERIC;
v_layover_hours := ROUND(v_layover_duration / 60.0, 1);
IF v_layover_index > 0 THEN
v_description := v_description || ', ';
END IF;
v_description := v_description || v_layover_name || ' (' || v_layover_code || ') ' ||
v_layover_hours || 'h';
v_layover_index := v_layover_index + 1;
END LOOP;
END IF;
END IF;
v_description := v_description || '.';
RETURN v_description;
END;
$function$
;This works fine when using a db tools like DBeaver to call the function. However, running this with Home Assistant does not work.
Calling the function from Home Assistant does run the function, but since HA is NOT doing an auto-commit, the results are not stored in the table. You can see it tried to do something because the primary key sequence has incremented for the number of flights saved.
Tried fixes:
-
Adding 'COMMIT' to PSQL function.
Does not work since in Postgresql it is NOT possible to do a 'COMMIT' inside the function.
-
Adding 'BEGIN' 'COMMIT' to HA SQL query
Does not work since HA does not allow multiline queries in the 'data[query]' value.
-
Creating a stored procedure in Postgres to call the function and do a COMMIT.
CREATE OR REPLACE PROCEDURE public.fetch_and_save_flight_prices_proc(
p_departure CHARACTER VARYING,
p_arrival CHARACTER VARYING,
p_outbound_date DATE,
p_return_date DATE,
p_num_passengers INTEGER,
OUT result_data JSON -- or JSONB
)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT json_agg(row_to_json(t))
INTO result_data
FROM (
SELECT *
FROM fetch_and_save_flight_prices(
p_departure,
p_arrival,
p_outbound_date,
p_return_date,
p_num_passengers
)
) t;
COMMIT;
END;
$$;This procedure calls the function and should return the result as JSON. However, this also does not work since HA does not support calling stored procedures.
-
Set 'autocommit=true' in HA db_url
There is no property of the SQL integration to set the auto_commit value. Extending the db_url with a query parameter like
&autocommit=trueorisolation_level=AUTOCOMMIT(from SQL Alchemy docs) both do not work.
Here my solutions kind of ended. I created a discussion on the Home Assistant Github repo to ask for support for auto_commit or procedure calls.
Before you can use the data on a HA dashboard, the SQL integration has to be added.
The SQL integration cannot be added via the UI, so this need to be added to the configuration.yaml manually.
This is easy with the 'File Editor' addon in HA (if you're not running HA as a docker container).
sql:
- name: "Flight Prices JSON"
unique_id: flight_prices_json
db_url: postgresql://USER:PASS@HOST/flightprices?client_encoding=UTF8
query: >
SELECT json_agg(row_to_json(t)) as data
FROM (
SELECT
CASE
WHEN layover_airports IS NOT NULL AND layover_airports != ''
THEN departure_airport || ' -> ' ||
replace(layover_airports, ',', ' -> ') || ' -> ' ||
arrival_airport
ELSE departure_airport || ' -> ' || arrival_airport
END as route,
to_char(outbound_date, 'Mon DD') as outbound,
CASE WHEN return_date IS NOT NULL
THEN to_char(return_date, 'Mon DD')
ELSE 'One-way' END as return,
'$' || total_price::text as price,
CASE WHEN price_per_person IS NOT NULL
THEN '$' || price_per_person::text
ELSE '-' END as price_pp,
is_best_price as best,
airlines,
num_stops as stops,
has_overnight_layover as overnight,
to_char(search_date, 'Mon DD HH24:MI') as searched,
notes
FROM flight_prices
ORDER BY price_pp asc, search_date DESC
LIMIT 10
) t
column: "data"This creates an entity in HA containing the data. There does not seem to be a way to directly query for data from a UI component. An alternative would be to use a Grafana table panel just like in Visualize in a graph.
This table shows the columns Route, Outbound, Return, Price, Per Person (price), Stops, Best, Overnight and Search date. The route is from departure airport to destination airport with possible layover airports in between.
type: custom:flex-table-card
entities:
include: sensor.flight_prices_json
columns:
- name: Route
data: data
modify: x.route
- name: Outbound
data: data
modify: x.outbound
- name: Return
data: data
modify: x.return
- name: Price
data: data
modify: x.price
- name: Per Person
data: data
modify: x.price_pp
- name: Stops
data: data
modify: x.stops
- name: Best
data: data
modify: if(x.best){'✓'}else{''}
- name: Overnight
data: data
modify: if(x.overnight){'✓'}else{''}
- name: Searched
data: data
modify: x.searched
cards:
- type: heading
heading: Latest Flight SearchesThis second table just shows the Route, Outbound date, Price, Price p.p. and notes. This note described the flight including stops.
These notes are added during the processing of the found flights using the create_flight_description PSQL function.
type: custom:flex-table-card
entities:
include: sensor.flight_prices_json
columns:
- name: Route
data: data
modify: x.route
- name: Outbound
data: data
modify: x.outbound
- name: Price
data: data
modify: x.price
- name: Price P.P.
data: data
modify: x.price_pp
- name: Notes
data: data
modify: x.notes || '-'
css:
td.notes: "white-space: normal; max-width: 300px;"
cards:
- type: heading
heading: Flight NotesQuery to select data for the panel:
SELECT
search_date AS "time",
total_price AS "value",
(
CASE
WHEN layover_airports IS NOT NULL AND layover_airports <> ''
THEN departure_airport || ' → ' ||
replace(layover_airports, ',', ' → ') || ' → ' ||
arrival_airport
ELSE departure_airport || ' → ' || arrival_airport
END
|| ' | ' || outbound_date
) AS "metric"
FROM flight_prices
ORDER BY search_date;Grafana panel
{
"id": 1,
"type": "timeseries",
"title": "Vlucht prijzen over tijd",
"description": "Toont hoe vlucht prijzen wijzigen over tijd voor een bepaalde route op bepaalde vertrekdatum",
"gridPos": {
"x": 0,
"y": 8,
"h": 8,
"w": 12
},
"fieldConfig": {
"defaults": {
"custom": {
"drawStyle": "line",
"lineInterpolation": "linear",
"barAlignment": 0,
"barWidthFactor": 0.6,
"lineWidth": 1,
"fillOpacity": 0,
"gradientMode": "none",
"spanNulls": true,
"insertNulls": false,
"showPoints": "auto",
"showValues": false,
"pointSize": 5,
"stacking": {
"mode": "none",
"group": "A"
},
"axisPlacement": "auto",
"axisLabel": "Prijs",
"axisColorMode": "text",
"axisBorderShow": false,
"scaleDistribution": {
"type": "linear"
},
"axisCenteredZero": false,
"hideFrom": {
"tooltip": false,
"viz": false,
"legend": false
},
"thresholdsStyle": {
"mode": "off"
}
},
"color": {
"mode": "palette-classic"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
},
"decimals": 3,
"fieldMinMax": false,
"min": 0,
"unit": "currencyEUR"
},
"overrides": []
},
"transformations": [
{
"disabled": true,
"id": "formatTime",
"options": {
"outputFormat": "DD-MM",
"timeField": "Time",
"timezone": "Europe/Amsterdam",
"useTimezone": true
}
}
],
"transparent": true,
"pluginVersion": "12.3.0",
"targets": [
{
"dataset": "flightprices",
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "dfb0tlhgotw5ca"
},
"editorMode": "code",
"format": "time_series",
"rawQuery": true,
"rawSql": "SELECT\n search_date AS \"time\",\n total_price AS \"value\",\n (\n CASE \n WHEN layover_airports IS NOT NULL AND layover_airports <> '' \n THEN departure_airport || ' → ' ||\n replace(layover_airports, ',', ' → ') || ' → ' ||\n arrival_airport\n ELSE departure_airport || ' → ' || arrival_airport\n END\n || ' | ' || outbound_date\n ) AS \"metric\"\nFROM flight_prices\nORDER BY search_date;\n",
"refId": "A",
"sql": {
"columns": [
{
"parameters": [],
"type": "function"
}
],
"groupBy": [
{
"property": {
"type": "string"
},
"type": "groupBy"
}
],
"limit": 50
}
}
],
"datasource": {
"type": "grafana-postgresql-datasource",
"uid": "dfb0tlhgotw5ca"
},
"interval": "1h",
"options": {
"tooltip": {
"mode": "single",
"sort": "none",
"hideZeros": false
},
"legend": {
"showLegend": true,
"displayMode": "list",
"placement": "bottom",
"calcs": []
}
}
}
To display on a HA dashboard, embed the panel using an iFrame.
Get the link to the panel from Grafana. When using the Grafana addon, the link probably uses the host localhost:3000.
Change this to the same hostname and port as on which you access Home Assistant.
Dashboard Grafana iframe
type: iframe
url: >-
http://<your-host>:<port>/api/hassio_ingress/s9L_qHjfSsl4oDmbPXiLrYbCNB_ZlnNpDNN6QTmM9xM/d-solo/ads7dj9/flight-prices?orgId=1&from=1768558057456&to=1769162857456&timezone=browser&tab=transformations&showCategory=Panel%20options&panelId=panel-1&__feature.dashboardSceneSolo=true
aspect_ratio: 75%
Screen shot Home Assistant dashboard with tables and graph panel:
