User-Defined Functions (UDFs)

<h2 id="user-defined-functions-in-geode" class="position-relative d-flex align-items-center group"> User-Defined Functions in Geode <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="user-defined-functions-in-geode" aria-haspopup="dialog" aria-label="Share link: User-Defined Functions in Geode"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>User-defined functions (UDFs) extend Geode’s built-in GQL capabilities with custom logic tailored to your application’s needs. Whether you need specialized data transformations, complex business rules, or domain-specific calculations, UDFs enable you to encapsulate reusable logic that executes directly within the database engine. Geode supports several types of custom functions: scalar functions that operate on individual values, aggregation functions that process sets of values, and stored procedures for complex multi-step operations. <h3 id="function-types-overview" class="position-relative d-flex align-items-center group"> Function Types Overview <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="function-types-overview" aria-haspopup="dialog" aria-label="Share link: Function Types Overview"> Share link </button> </h3> <h4 id="scalar-functions" class="position-relative d-flex align-items-center group"> Scalar Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="scalar-functions" aria-haspopup="dialog" aria-label="Share link: Scalar Functions"> Share link </button> </h4>Scalar functions take one or more input values and return a single value. They can be used anywhere expressions are allowed. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Using a custom scalar function MATCH (u:User) RETURN u.name, normalize_phone(u.phone) AS phone, calculate_age(u.birth_date) AS age; </code></pre></div> <h4 id="aggregation-functions" class="position-relative d-flex align-items-center group"> Aggregation Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="aggregation-functions" aria-haspopup="dialog" aria-label="Share link: Aggregation Functions"> Share link </button> </h4>Aggregation functions process multiple input values and return a single aggregated result. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Using a custom aggregation function MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN c.name, weighted_average(p.rating, p.review_count) AS avg_rating; </code></pre></div> <h4 id="stored-procedures" class="position-relative d-flex align-items-center group"> Stored Procedures <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="stored-procedures" aria-haspopup="dialog" aria-label="Share link: Stored Procedures"> Share link </button> </h4>Stored procedures encapsulate complex multi-statement logic that may modify data. <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Calling a stored procedure CALL calculate_user_scores(); -- Procedure with parameters and return values CALL find_influencers($min_followers, $category) YIELD user_id, influence_score; </code></pre></div> <h3 id="creating-scalar-functions" class="position-relative d-flex align-items-center group"> Creating Scalar Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="creating-scalar-functions" aria-haspopup="dialog" aria-label="Share link: Creating Scalar Functions"> Share link </button> </h3> <h4 id="basic-scalar-function" class="position-relative d-flex align-items-center group"> Basic Scalar Function <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="basic-scalar-function" aria-haspopup="dialog" aria-label="Share link: Basic Scalar Function"> Share link </button> </h4>Define a function that normalizes phone numbers: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create a scalar function CREATE FUNCTION normalize_phone(phone STRING) RETURNS STRING LANGUAGE GQL AS $$ -- Remove all non-digit characters WITH regexp_replace(phone, '[^0-9]', '') AS digits RETURN CASE WHEN length(digits) = 10 THEN '(' + substring(digits, 0, 3) + ') ' + substring(digits, 3, 3) + '-' + substring(digits, 6, 4) WHEN length(digits) = 11 AND substring(digits, 0, 1) = '1' THEN '+1 (' + substring(digits, 1, 3) + ') ' + substring(digits, 4, 3) + '-' + substring(digits, 7, 4) ELSE phone END $$; -- Use the function MATCH (c:Contact) RETURN c.name, normalize_phone(c.phone) AS formatted_phone; </code></pre></div> <h4 id="function-with-multiple-parameters" class="position-relative d-flex align-items-center group"> Function with Multiple Parameters <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="function-with-multiple-parameters" aria-haspopup="dialog" aria-label="Share link: Function with Multiple Parameters"> Share link </button> </h4>Calculate distance between geographic coordinates: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Haversine distance function CREATE FUNCTION haversine_distance( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS FLOAT LANGUAGE GQL AS $$ WITH 6371.0 AS earth_radius_km, -- Earth's radius radians(lat1) AS lat1_rad, radians(lat2) AS lat2_rad, radians(lat2 - lat1) AS delta_lat, radians(lon2 - lon1) AS delta_lon WITH earth_radius_km, lat1_rad, lat2_rad, delta_lat, delta_lon, sin(delta_lat / 2) * sin(delta_lat / 2) + cos(lat1_rad) * cos(lat2_rad) * sin(delta_lon / 2) * sin(delta_lon / 2) AS a WITH earth_radius_km, 2 * atan2(sqrt(a), sqrt(1 - a)) AS c RETURN earth_radius_km * c $$; -- Find locations within radius MATCH (store:Store) WHERE haversine_distance($user_lat, $user_lon, store.latitude, store.longitude) < 10 RETURN store.name, store.address ORDER BY haversine_distance($user_lat, $user_lon, store.latitude, store.longitude); </code></pre></div> <h4 id="function-with-default-parameters" class="position-relative d-flex align-items-center group"> Function with Default Parameters <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="function-with-default-parameters" aria-haspopup="dialog" aria-label="Share link: Function with Default Parameters"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Function with default values CREATE FUNCTION format_currency( amount FLOAT, currency STRING DEFAULT 'USD', decimals INT DEFAULT 2 ) RETURNS STRING LANGUAGE GQL AS $$ WITH CASE currency WHEN 'USD' THEN '$' WHEN 'EUR' THEN '\u20AC' WHEN 'GBP' THEN '\u00A3' WHEN 'JPY' THEN '\u00A5' ELSE currency + ' ' END AS symbol RETURN symbol + round(amount, decimals) $$; -- Usage with defaults SELECT format_currency(99.99); -- '$99.99' SELECT format_currency(99.99, 'EUR'); -- '99.99' SELECT format_currency(1234.567, 'USD', 0); -- '$1235' </code></pre></div> <h4 id="function-with-complex-logic" class="position-relative d-flex align-items-center group"> Function with Complex Logic <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="function-with-complex-logic" aria-haspopup="dialog" aria-label="Share link: Function with Complex Logic"> Share link </button> </h4>Calculate credit score tier: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE FUNCTION credit_tier(score INT) RETURNS STRING LANGUAGE GQL AS $$ RETURN CASE WHEN score >= 800 THEN 'Excellent' WHEN score >= 740 THEN 'Very Good' WHEN score >= 670 THEN 'Good' WHEN score >= 580 THEN 'Fair' WHEN score >= 300 THEN 'Poor' ELSE 'Invalid' END $$; -- Use in queries MATCH (c:Customer) RETURN c.name, c.credit_score, credit_tier(c.credit_score) AS tier; -- Use in filtering MATCH (c:Customer) WHERE credit_tier(c.credit_score) IN ['Excellent', 'Very Good'] RETURN c.name, c.credit_limit; </code></pre></div> <h3 id="creating-aggregation-functions" class="position-relative d-flex align-items-center group"> Creating Aggregation Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="creating-aggregation-functions" aria-haspopup="dialog" aria-label="Share link: Creating Aggregation Functions"> Share link </button> </h3> <h4 id="custom-average-with-null-handling" class="position-relative d-flex align-items-center group"> Custom Average with Null Handling <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="custom-average-with-null-handling" aria-haspopup="dialog" aria-label="Share link: Custom Average with Null Handling"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Weighted average that handles nulls CREATE AGGREGATE FUNCTION weighted_avg( value FLOAT, weight FLOAT ) RETURNS FLOAT STATE (sum FLOAT, weight_sum FLOAT) INIT $$ SET sum = 0.0, weight_sum = 0.0 $$ ACCUMULATE $$ IF value IS NOT NULL AND weight IS NOT NULL THEN SET sum = sum + (value * weight), weight_sum = weight_sum + weight END IF $$ FINALIZE $$ RETURN CASE WHEN weight_sum > 0 THEN sum / weight_sum ELSE NULL END $$; -- Usage MATCH (p:Product) RETURN weighted_avg(p.rating, p.review_count) AS weighted_rating; -- Group by category MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN c.name, weighted_avg(p.rating, p.review_count) AS category_rating ORDER BY category_rating DESC; </code></pre></div> <h4 id="percentile-calculation" class="position-relative d-flex align-items-center group"> Percentile Calculation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="percentile-calculation" aria-haspopup="dialog" aria-label="Share link: Percentile Calculation"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Percentile aggregation function CREATE AGGREGATE FUNCTION percentile( value FLOAT, percentile FLOAT ) RETURNS FLOAT STATE (values LIST<FLOAT>) INIT $$ SET values = [] $$ ACCUMULATE $$ IF value IS NOT NULL THEN SET values = values + [value] END IF $$ FINALIZE $$ WITH size(values) AS n WHERE n > 0 WITH values, percentile * (n - 1) AS idx WITH values, floor(idx) AS lower_idx, ceil(idx) AS upper_idx, idx - floor(idx) AS frac -- Sort values WITH [v IN values ORDER BY v] AS sorted, lower_idx, upper_idx, frac RETURN CASE WHEN lower_idx = upper_idx THEN sorted[lower_idx] ELSE sorted[lower_idx] * (1 - frac) + sorted[upper_idx] * frac END $$; -- Calculate median (50th percentile) MATCH (e:Employee) RETURN percentile(e.salary, 0.5) AS median_salary; -- Multiple percentiles MATCH (o:Order) RETURN percentile(o.total, 0.25) AS p25, percentile(o.total, 0.50) AS median, percentile(o.total, 0.75) AS p75, percentile(o.total, 0.90) AS p90; </code></pre></div> <h4 id="mode-most-frequent-value" class="position-relative d-flex align-items-center group"> Mode (Most Frequent Value) <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="mode-most-frequent-value" aria-haspopup="dialog" aria-label="Share link: Mode (Most Frequent Value)"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">CREATE AGGREGATE FUNCTION mode(value ANY) RETURNS ANY STATE (counts MAP<ANY, INT>) INIT $$ SET counts = {} $$ ACCUMULATE $$ IF value IS NOT NULL THEN SET counts[value] = COALESCE(counts[value], 0) + 1 END IF $$ FINALIZE $$ WITH [entry IN counts | entry] AS entries UNWIND entries AS entry WITH entry ORDER BY entry.value DESC LIMIT 1 RETURN entry.key $$; -- Find most common category MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN mode(c.name) AS most_common_category; </code></pre></div> <h4 id="string-aggregation" class="position-relative d-flex align-items-center group"> String Aggregation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="string-aggregation" aria-haspopup="dialog" aria-label="Share link: String Aggregation"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Concatenate strings with separator CREATE AGGREGATE FUNCTION string_agg( value STRING, separator STRING DEFAULT ', ' ) RETURNS STRING STATE (values LIST<STRING>) INIT $$ SET values = [] $$ ACCUMULATE $$ IF value IS NOT NULL THEN SET values = values + [value] END IF $$ FINALIZE $$ RETURN reduce(result = '', i IN range(0, size(values) - 1) | result + CASE WHEN i = 0 THEN '' ELSE separator END + values[i] ) $$; -- Usage MATCH (o:Order)-[:CONTAINS]->(p:Product) RETURN o.id, string_agg(p.name, ', ') AS products; -- Custom separator MATCH (u:User)-[:HAS_SKILL]->(s:Skill) RETURN u.name, string_agg(s.name, ' | ') AS skills; </code></pre></div> <h3 id="creating-stored-procedures" class="position-relative d-flex align-items-center group"> Creating Stored Procedures <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="creating-stored-procedures" aria-haspopup="dialog" aria-label="Share link: Creating Stored Procedures"> Share link </button> </h3> <h4 id="basic-procedure" class="position-relative d-flex align-items-center group"> Basic Procedure <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="basic-procedure" aria-haspopup="dialog" aria-label="Share link: Basic Procedure"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Procedure to archive old orders CREATE PROCEDURE archive_old_orders(cutoff_date DATE) LANGUAGE GQL AS $$ -- Move old orders to archive MATCH (o:Order) WHERE o.order_date < cutoff_date AND o.status = 'completed' SET o:ArchivedOrder REMOVE o:Order; -- Return count of archived orders MATCH (a:ArchivedOrder) WHERE a.archived_at IS NULL SET a.archived_at = datetime() RETURN COUNT(a) AS archived_count; $$; -- Call the procedure CALL archive_old_orders(DATE '2024-01-01'); </code></pre></div> <h4 id="procedure-with-yield" class="position-relative d-flex align-items-center group"> Procedure with YIELD <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="procedure-with-yield" aria-haspopup="dialog" aria-label="Share link: Procedure with YIELD"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Procedure that yields multiple result sets CREATE PROCEDURE find_influencers( min_followers INT, category STRING DEFAULT NULL ) RETURNS (user_id STRING, username STRING, followers INT, influence_score FLOAT) LANGUAGE GQL AS $$ MATCH (u:User) WHERE u.followers_count >= min_followers OPTIONAL MATCH (u)-[:POSTS_IN]->(c:Category) WHERE category IS NULL OR c.name = category WITH u, u.followers_count AS followers, u.followers_count * 1.0 / (u.following_count + 1) AS ratio WHERE category IS NULL OR c IS NOT NULL RETURN u.id AS user_id, u.username AS username, followers, ratio * log(followers + 1) AS influence_score ORDER BY influence_score DESC $$; -- Use with YIELD CALL find_influencers(1000, 'Technology') YIELD user_id, username, influence_score WHERE influence_score > 100 RETURN user_id, username, influence_score; </code></pre></div> <h4 id="procedure-for-data-maintenance" class="position-relative d-flex align-items-center group"> Procedure for Data Maintenance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="procedure-for-data-maintenance" aria-haspopup="dialog" aria-label="Share link: Procedure for Data Maintenance"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Cleanup procedure for expired sessions CREATE PROCEDURE cleanup_expired_sessions() RETURNS (deleted_count INT, freed_mb FLOAT) LANGUAGE GQL AS $$ -- Find expired sessions MATCH (s:Session) WHERE s.expires_at < datetime() WITH COLLECT(s) AS expired_sessions -- Delete associated data UNWIND expired_sessions AS session OPTIONAL MATCH (session)-[:HAS_CACHE]->(cache) DELETE cache -- Delete sessions WITH expired_sessions UNWIND expired_sessions AS session DELETE session -- Return statistics RETURN SIZE(expired_sessions) AS deleted_count, 0.0 AS freed_mb; -- Placeholder for actual size calculation $$; -- Schedule cleanup CALL cleanup_expired_sessions() YIELD deleted_count RETURN deleted_count; </code></pre></div> <h4 id="transaction-control-in-procedures" class="position-relative d-flex align-items-center group"> Transaction Control in Procedures <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="transaction-control-in-procedures" aria-haspopup="dialog" aria-label="Share link: Transaction Control in Procedures"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Procedure with explicit transaction handling CREATE PROCEDURE transfer_funds( from_account_id STRING, to_account_id STRING, amount FLOAT ) RETURNS (success BOOLEAN, message STRING) LANGUAGE GQL AS $$ -- Validate amount IF amount <= 0 THEN RETURN false, 'Amount must be positive'; END IF; -- Check source account balance MATCH (source:Account {id: from_account_id}) IF source IS NULL THEN RETURN false, 'Source account not found'; END IF; IF source.balance < amount THEN RETURN false, 'Insufficient funds'; END IF; -- Check destination account MATCH (dest:Account {id: to_account_id}) IF dest IS NULL THEN RETURN false, 'Destination account not found'; END IF; -- Perform transfer SET source.balance = source.balance - amount; SET dest.balance = dest.balance + amount; -- Create transaction record CREATE (t:Transaction { id: randomUUID(), from_account: from_account_id, to_account: to_account_id, amount: amount, timestamp: datetime() }); RETURN true, 'Transfer completed successfully'; $$; -- Use the procedure CALL transfer_funds('ACC-001', 'ACC-002', 500.00) YIELD success, message RETURN success, message; </code></pre></div> <h3 id="function-management" class="position-relative d-flex align-items-center group"> Function Management <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="function-management" aria-haspopup="dialog" aria-label="Share link: Function Management"> Share link </button> </h3> <h4 id="listing-functions" class="position-relative d-flex align-items-center group"> Listing Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="listing-functions" aria-haspopup="dialog" aria-label="Share link: Listing Functions"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- List all custom functions SHOW FUNCTIONS; -- List functions by type SHOW FUNCTIONS WHERE type = 'SCALAR'; SHOW FUNCTIONS WHERE type = 'AGGREGATE'; -- Get function definition SHOW FUNCTION normalize_phone; </code></pre></div> <h4 id="modifying-functions" class="position-relative d-flex align-items-center group"> Modifying Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="modifying-functions" aria-haspopup="dialog" aria-label="Share link: Modifying Functions"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Replace existing function (CREATE OR REPLACE) CREATE OR REPLACE FUNCTION normalize_phone(phone STRING) RETURNS STRING LANGUAGE GQL AS $$ -- Updated implementation WITH regexp_replace(phone, '[^0-9+]', '') AS cleaned RETURN CASE WHEN length(cleaned) = 10 THEN '+1' + cleaned ELSE cleaned END $$; -- Drop function DROP FUNCTION normalize_phone; -- Drop if exists DROP FUNCTION IF EXISTS normalize_phone; </code></pre></div> <h4 id="function-permissions" class="position-relative d-flex align-items-center group"> Function Permissions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="function-permissions" aria-haspopup="dialog" aria-label="Share link: Function Permissions"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Grant execute permission GRANT EXECUTE ON FUNCTION normalize_phone TO role_analyst; -- Revoke permission REVOKE EXECUTE ON FUNCTION transfer_funds FROM role_user; -- Set function as SECURITY DEFINER (runs with creator's permissions) CREATE FUNCTION admin_task() RETURNS BOOLEAN SECURITY DEFINER LANGUAGE GQL AS $$ -- Runs with elevated permissions RETURN true $$; </code></pre></div> <h3 id="advanced-patterns" class="position-relative d-flex align-items-center group"> Advanced Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="advanced-patterns" aria-haspopup="dialog" aria-label="Share link: Advanced Patterns"> Share link </button> </h3> <h4 id="recursive-functions" class="position-relative d-flex align-items-center group"> Recursive Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="recursive-functions" aria-haspopup="dialog" aria-label="Share link: Recursive Functions"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Calculate factorial CREATE FUNCTION factorial(n INT) RETURNS INT LANGUAGE GQL AS $$ RETURN CASE WHEN n <= 1 THEN 1 ELSE n * factorial(n - 1) END $$; -- Fibonacci (with memoization consideration) CREATE FUNCTION fibonacci(n INT) RETURNS INT LANGUAGE GQL AS $$ RETURN CASE WHEN n <= 0 THEN 0 WHEN n = 1 THEN 1 ELSE fibonacci(n - 1) + fibonacci(n - 2) END $$; </code></pre></div> <h4 id="functions-with-graph-traversal" class="position-relative d-flex align-items-center group"> Functions with Graph Traversal <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="functions-with-graph-traversal" aria-haspopup="dialog" aria-label="Share link: Functions with Graph Traversal"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Calculate shortest path length CREATE FUNCTION path_length(from_id STRING, to_id STRING, max_hops INT DEFAULT 10) RETURNS INT LANGUAGE GQL AS $$ MATCH path = shortestPath( (a {id: from_id})-[*..max_hops]->(b {id: to_id}) ) RETURN COALESCE(length(path), -1) $$; -- Check if nodes are connected CREATE FUNCTION is_connected(from_id STRING, to_id STRING) RETURNS BOOLEAN LANGUAGE GQL AS $$ RETURN EXISTS { MATCH (a {id: from_id})-[*..20]-(b {id: to_id}) } $$; -- Usage MATCH (u1:User), (u2:User) WHERE u1 <> u2 RETURN u1.name, u2.name, path_length(u1.id, u2.id) AS degrees_of_separation ORDER BY degrees_of_separation LIMIT 20; </code></pre></div> <h4 id="domain-specific-functions" class="position-relative d-flex align-items-center group"> Domain-Specific Functions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="domain-specific-functions" aria-haspopup="dialog" aria-label="Share link: Domain-Specific Functions"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- E-commerce: Calculate shipping cost CREATE FUNCTION calculate_shipping( weight FLOAT, distance FLOAT, expedited BOOLEAN DEFAULT false ) RETURNS FLOAT LANGUAGE GQL AS $$ WITH 0.50 AS base_per_lb, 0.01 AS base_per_mile, 1.5 AS expedited_multiplier WITH weight * base_per_lb + distance * base_per_mile AS base_cost RETURN CASE WHEN expedited THEN base_cost * expedited_multiplier ELSE base_cost END $$; -- Finance: Calculate compound interest CREATE FUNCTION compound_interest( principal FLOAT, rate FLOAT, periods INT, compounds_per_period INT DEFAULT 12 ) RETURNS FLOAT LANGUAGE GQL AS $$ RETURN principal * power( 1 + rate / compounds_per_period, compounds_per_period * periods ) $$; -- Healthcare: Calculate BMI CREATE FUNCTION calculate_bmi( weight_kg FLOAT, height_cm FLOAT ) RETURNS FLOAT LANGUAGE GQL AS $$ WITH height_cm / 100.0 AS height_m RETURN round(weight_kg / (height_m * height_m), 1) $$; </code></pre></div> <h3 id="client-integration-examples" class="position-relative d-flex align-items-center group"> Client Integration Examples <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="client-integration-examples" aria-haspopup="dialog" aria-label="Share link: Client Integration Examples"> Share link </button> </h3> <h4 id="python-client" class="position-relative d-flex align-items-center group"> Python Client <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="python-client" aria-haspopup="dialog" aria-label="Share link: Python Client"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">from geode_client import Client async def use_custom_functions(): client = Client(host="localhost", port=3141) async with client.connection() as conn: # Create a custom function await conn.execute(""" CREATE OR REPLACE FUNCTION format_name(first STRING, last STRING) RETURNS STRING LANGUAGE GQL AS $$ RETURN upper(substring(first, 0, 1)) + '. ' + last $$ """) # Use the function in queries result, _ = await conn.query(""" MATCH (e:Employee) RETURN format_name(e.first_name, e.last_name) AS display_name, e.department ORDER BY e.last_name """) for row in result.rows: print(f"{row['display_name']} - {row['department']}") # Call a stored procedure result, _ = await conn.query(""" CALL find_influencers($min_followers, $category) YIELD user_id, username, influence_score WHERE influence_score > $min_score RETURN user_id, username, influence_score """, { 'min_followers': 1000, 'category': 'Technology', 'min_score': 50 }) for row in result.rows: print(f"Influencer: {row['username']} (score: {row['influence_score']:.2f})") </code></pre></div> <h4 id="go-client" class="position-relative d-flex align-items-center group"> Go Client <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="go-client" aria-haspopup="dialog" aria-label="Share link: Go Client"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-go" data-lang="go">package main import ( "context" "database/sql" "log" _ "geodedb.com/geode" ) func useCustomFunctions(ctx context.Context, db *sql.DB) error { // Create function _, err := db.ExecContext(ctx, ` CREATE OR REPLACE FUNCTION days_until(target_date DATE) RETURNS INT LANGUAGE GQL AS $$ RETURN date_diff('day', current_date(), target_date) $$ `) if err != nil { return err } // Use in query rows, err := db.QueryContext(ctx, ` MATCH (e:Event) WHERE e.event_date > current_date() RETURN e.name, e.event_date, days_until(e.event_date) AS days_away ORDER BY days_away `) if err != nil { return err } defer rows.Close() for rows.Next() { var name string var eventDate string var daysAway int rows.Scan(&name, &eventDate, &daysAway) log.Printf("%s on %s (%d days away)", name, eventDate, daysAway) } return nil } </code></pre></div> <h4 id="rust-client" class="position-relative d-flex align-items-center group"> Rust Client <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="rust-client" aria-haspopup="dialog" aria-label="Share link: Rust Client"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-rust" data-lang="rust">use geode_client::{Client, Value}; async fn use_custom_functions(client: &Client) -> Result<(), Box<dyn std::error::Error>> { // Create custom aggregation function client.execute( r#" CREATE OR REPLACE AGGREGATE FUNCTION geometric_mean(value FLOAT) RETURNS FLOAT STATE (product FLOAT, count INT) INIT $$ SET product = 1.0, count = 0 $$ ACCUMULATE $$ IF value IS NOT NULL AND value > 0 THEN SET product = product * value, count = count + 1 END IF $$ FINALIZE $$ RETURN CASE WHEN count > 0 THEN power(product, 1.0 / count) ELSE NULL END $$ "#, &[], ).await?; // Use the function let results = client.query( r#" MATCH (p:Product)-[:IN_CATEGORY]->(c:Category) RETURN c.name, geometric_mean(p.rating) AS geo_mean_rating, AVG(p.rating) AS arithmetic_mean ORDER BY geo_mean_rating DESC "#, &[], ).await?; for row in results.rows() { println!( "{}: geometric={:.2}, arithmetic={:.2}", row.get::<String>("c.name")?, row.get::<f64>("geo_mean_rating")?, row.get::<f64>("arithmetic_mean")? ); } Ok(()) } </code></pre></div> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="best-practices" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3> <h4 id="function-design" class="position-relative d-flex align-items-center group"> Function Design <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="function-design" aria-haspopup="dialog" aria-label="Share link: Function Design"> Share link </button> </h4><ul> <li>Keep functions focused on a single task</li> <li>Use descriptive parameter names</li> <li>Provide sensible default values</li> <li>Document expected input/output formats</li> <li>Handle null values explicitly</li> </ul> <h4 id="performance-considerations" class="position-relative d-flex align-items-center group"> Performance Considerations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-considerations" aria-haspopup="dialog" aria-label="Share link: Performance Considerations"> Share link </button> </h4><ul> <li>Avoid expensive operations in frequently-called functions</li> <li>Use indexes for graph traversals within functions</li> <li>Consider caching for deterministic functions</li> <li>Profile function performance with EXPLAIN/PROFILE</li> </ul> <h4 id="security" class="position-relative d-flex align-items-center group"> Security <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="security" aria-haspopup="dialog" aria-label="Share link: Security"> Share link </button> </h4><ul> <li>Validate all input parameters</li> <li>Use SECURITY DEFINER carefully</li> <li>Limit function permissions appropriately</li> <li>Avoid SQL injection in dynamic queries</li> </ul> <h4 id="testing" class="position-relative d-flex align-items-center group"> Testing <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="testing" aria-haspopup="dialog" aria-label="Share link: Testing"> Share link </button> </h4><ul> <li>Test with edge cases (nulls, empty strings, extreme values)</li> <li>Verify error handling</li> <li>Test with realistic data volumes</li> <li>Include performance benchmarks</li> </ul> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="related-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li><a href="/tags/functions/" >Functions</a> - Built-in GQL functions</li> <li><a href="/tags/stored-procedures/" >Stored Procedures</a> - Procedure documentation</li> <li><a href="/tags/gql-reference/" >GQL Reference</a> - GQL syntax reference</li> <li><a href="/tags/advanced/" >Advanced</a> - Advanced features</li> <li><a href="/tags/performance/" >Performance</a> - Performance optimization</li> <li><a href="/tags/security/" >Security</a> - Security best practices</li> </ul> <h3 id="further-reading" class="position-relative d-flex align-items-center group"> Further Reading <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="further-reading" aria-haspopup="dialog" aria-label="Share link: Further Reading"> Share link </button> </h3><ul> <li>ISO/IEC 39075:2024 User-Defined Functions</li> <li>GQL Function Development Guide</li> <li>Performance Optimization for UDFs</li> <li>Geode Extension Architecture</li> <li>Custom Function Security Considerations</li> </ul> Browse tagged content for complete user-defined function documentation and examples.

Popular

Related Articles