Skip to main content
Open in Kaggle  Open in Colab  Download Notebook
This documentation page is also available as an interactive notebook. You can launch the notebook in Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the above links.
Create lookup functions that query tables by key—for customer records, product catalogs, or financial data.

​
Problem

You have structured data—customer records, product catalogs, financial data—and need to look up rows by key values. Common scenarios:

​
Solution

What’s in this recipe:
  • Create lookup functions from tables with retrieval_udf
  • Query by single or multiple keys
  • Use lookups in computed columns for data enrichment
Use pxt.retrieval_udf(table) to automatically create a function that queries the table by its columns.

​
Setup

%pip install -qU pixeltable

import pixeltable as pxt

# Create a fresh directory
pxt.drop_dir('lookup_demo', force=True)
pxt.create_dir('lookup_demo')

Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/pjlb/.pixeltable/pgdata
Created directory ‘lookup_demo’.
<pixeltable.catalog.dir.Dir at 0x143224e50>

​
Create a product catalog table

# Create a product catalog
products = pxt.create_table(
    'lookup_demo.products',
    {'sku': pxt.String, 'name': pxt.String, 'price': pxt.Float, 'category': pxt.String}
)

products.insert([
    {'sku': 'LAPTOP-001', 'name': 'MacBook Pro 14"', 'price': 1999.00, 'category': 'electronics'},
    {'sku': 'LAPTOP-002', 'name': 'ThinkPad X1', 'price': 1499.00, 'category': 'electronics'},
    {'sku': 'PHONE-001', 'name': 'iPhone 15 Pro', 'price': 999.00, 'category': 'electronics'},
    {'sku': 'CHAIR-001', 'name': 'Ergonomic Office Chair', 'price': 449.00, 'category': 'furniture'},
    {'sku': 'DESK-001', 'name': 'Standing Desk', 'price': 699.00, 'category': 'furniture'},
])

products.collect()

Created table ‘products’.
Inserting rows into `products`: 5 rows [00:00, 502.31 rows/s]
Inserted 5 rows with 0 errors.

​
Create a lookup function with retrieval_udf

# Create a lookup function that searches by SKU
get_product = pxt.retrieval_udf(
    products,
    name='get_product',
    description='Look up a product by its SKU code',
    parameters=['sku'],  # Only use SKU as the lookup key
    limit=1  # Return at most 1 result
)
# Check the function signature

# Look up a product by SKU
result = products.select(get_product(sku='LAPTOP-001')).limit(1).collect()

​
Look up by category (multiple results)

# Create a category lookup (returns multiple products)
get_by_category = pxt.retrieval_udf(
    products,
    name="get_by_category",
    description="Get all products in a category",
    parameters=["category"],
    limit=10  # Return up to 10 products
)

# Find all electronics
products.select(get_by_category(category="electronics")).limit(1).collect()

​
Use lookups for data enrichment

# Create an orders table
orders = pxt.create_table(
    'lookup_demo.orders',
    {'order_id': pxt.String, 'product_sku': pxt.String, 'quantity': pxt.Int}
)

orders.insert([
    {'order_id': 'ORD-001', 'product_sku': 'LAPTOP-001', 'quantity': 2},
    {'order_id': 'ORD-002', 'product_sku': 'PHONE-001', 'quantity': 1},
    {'order_id': 'ORD-003', 'product_sku': 'CHAIR-001', 'quantity': 4},
])

Created table ‘orders’.
Inserting rows into `orders`: 3 rows [00:00, 1186.28 rows/s]
Inserted 3 rows with 0 errors.
3 rows inserted, 6 values computed.
# Add a computed column that enriches orders with product details
orders.add_computed_column(product_info=get_product(sku=orders.product_sku))

# View enriched orders
orders.select(
    orders.order_id,
    orders.product_sku,
    orders.quantity,
    orders.product_info
).collect()

Added 3 column values with 0 errors.

​
Explanation

retrieval_udf parameters:
Use cases:
Tips:
  • Use limit=1 for unique key lookups
  • Specify only needed columns in parameters for cleaner APIs
  • Add descriptions for LLM tool integration

​
See also