How to fix relation does not have a composite type in PostgreSQL

CREATE TYPE person_type AS (
  id INT,
  name TEXT,
  age INT
);

CREATE FUNCTION process_person(p person_type) RETURNS TEXT AS $$
BEGIN
  RETURN p.name || ' is ' || p.age || ' years old';
END;
$$ LANGUAGE plpgsql;

SELECT process_person(ROW(id, name, age)::person_type) FROM person;

-- Or use CAST syntax
SELECT process_person(CAST(ROW(id, name, age) AS person_type)) FROM person;

CREATE TYPE user_record AS (
  id INT,
  name TEXT
);

SELECT array_agg(ROW(id, name)::user_record) FROM users;

-- Or in a function call
SELECT process_records(array_agg(ROW(id, name)::user_record))
FROM users;

CREATE TYPE order_item AS (
  order_id BIGINT,
  product_name VARCHAR(255),
  quantity INT,
  price NUMERIC(10,2),
  ordered_at TIMESTAMP
);

-- Use with casting
SELECT array_agg(
  ROW(
    o.id,
    p.name,
    oi.quantity,
    oi.unit_price,
    o.created_at
  )::order_item
)
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id;

-- This will not work properly with table rows
CREATE FUNCTION get_person_info(p person) RETURNS TEXT AS $$
BEGIN
  -- This references the table row type, not a stand-alone type
  RETURN p.name;
END;
$$ LANGUAGE plpgsql;

-- Step 1: Define the composite type first
CREATE TYPE person_type AS (
  id INT,
  name TEXT,
  email TEXT,
  age INT
);

-- Step 2: Define function using the composite type
CREATE FUNCTION get_person_info(p person_type)
RETURNS TEXT AS $$
BEGIN
  RETURN p.name || ' (' || p.email || ')';
END;
$$ LANGUAGE plpgsql;

-- Step 3: Call with proper casting
SELECT get_person_info(ROW(1, 'John', '[email protected]', 30)::person_type);

CREATE TYPE address_type AS (
  street VARCHAR(255),
  city VARCHAR(100),
  country VARCHAR(100),
  postal_code VARCHAR(20)
);

CREATE TYPE contact_type AS (
  email VARCHAR(255),
  phone VARCHAR(20)
);

CREATE FUNCTION create_customer(
  p person_type,
  addr address_type,
  contact contact_type
) RETURNS TABLE(customer_id INT, created_date TIMESTAMP) AS $$
BEGIN
  INSERT INTO customers (name, email, street, city, country, postal_code, phone)
  VALUES (p.name, contact.email, addr.street, addr.city, addr.country,
          addr.postal_code, contact.phone)
  RETURNING id, created_at;
END;
$$ LANGUAGE plpgsql;

CREATE TYPE employee_type AS (
  employee_id INT,
  name TEXT,
  department TEXT,
  salary NUMERIC(10,2),
  hire_date DATE
);

CREATE TABLE employees OF employee_type (
  PRIMARY KEY (employee_id),
  CHECK (salary > 0)
);

INSERT INTO employees VALUES
  (1, 'Alice', 'Engineering', 100000.00, '2020-01-15'),
  (2, 'Bob', 'Sales', 80000.00, '2021-06-01');

CREATE FUNCTION process_employee(e employee_type)
RETURNS VARCHAR AS $$
BEGIN
  RETURN e.name || ' works in ' || e.department;
END;
$$ LANGUAGE plpgsql;

-- This works with typed tables
SELECT process_employee(employees.*) FROM employees;

-- Simple and clear casting
CREATE TYPE payment AS (
  amount NUMERIC,
  currency VARCHAR(3),
  method VARCHAR(50)
);

SELECT array_agg(ROW(amount, 'USD', 'card')::payment)
FROM orders;

-- May need more verbose casting
SELECT array_agg(CAST(ROW(amount, 'USD', 'card') AS payment))
FROM orders;

psql -c "SELECT version();"

SHOW server_version;
-- Returns something like: 15.1

SELECT
  t.typname as type_name,
  t.typtype as type_type,
  a.attname as field_name,
  a.atttypid::regtype as field_type,
  a.attnum as field_number
FROM pg_type t
JOIN pg_attribute a ON a.attrelid = t.typrelid
WHERE t.typtype = 'c'
ORDER BY t.typname, a.attnum;

-- Check what types a function expects
SELECT
  p.proname as function_name,
  t.typname as parameter_type,
  p.proargtypes
FROM pg_proc p
JOIN pg_type t ON t.oid = ANY(p.proargtypes)
WHERE p.proname = 'process_person'
ORDER BY p.oid;

-- Find all functions that accept a specific composite type
SELECT DISTINCT
  p.proname,
  t.typname,
  pg_get_functiondef(p.oid) as definition
FROM pg_proc p,
  pg_type t
WHERE t.typname = 'person_type'
AND p.proargtypes::text LIKE '%' || t.oid::text || '%';

-- If you get the error, compare what exists vs. what is expected
-- First, find the relation
SELECT
  c.relname as table_or_view_name,
  c.relkind as relation_type
FROM pg_class c
WHERE c.relname = 'person';

-- Check if it has an associated composite type
SELECT
  c.relname,
  t.typname,
  t.oid
FROM pg_class c
JOIN pg_type t ON t.typrelid = c.oid
WHERE c.relname = 'person';