StoredProcedure¶

{
  "$id": "https://open-metadata.org/schema/entity/data/storedProcedure.json",
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "StoredProcedure",
  "description": "A `StoredProcedure` entity represents executable database logic including stored procedures, functions, and database routines.",
  "type": "object",
  "javaType": "org.openmetadata.schema.entity.data.StoredProcedure",

  "definitions": {
    "storedProcedureType": {
      "description": "Type of stored procedure",
      "type": "string",
      "enum": [
        "StoredProcedure", "Function", "Trigger",
        "AggregateFunction", "TableFunction", "ScalarFunction"
      ]
    },
    "storedProcedureLanguage": {
      "description": "Programming language",
      "type": "string",
      "enum": [
        "SQL", "PLSQL", "TSQL", "PL/pgSQL",
        "JavaScript", "Python", "Java", "Scala"
      ]
    },
    "parameter": {
      "type": "object",
      "properties": {
        "name": {
          "description": "Parameter name",
          "type": "string"
        },
        "dataType": {
          "description": "Data type",
          "type": "string"
        },
        "parameterMode": {
          "description": "Parameter mode",
          "type": "string",
          "enum": ["IN", "OUT", "INOUT"]
        },
        "description": {
          "description": "Parameter description",
          "type": "string"
        }
      }
    }
  },

  "properties": {
    "id": {
      "description": "Unique identifier",
      "$ref": "../../type/basic.json#/definitions/uuid"
    },
    "name": {
      "description": "Stored procedure name",
      "$ref": "../../type/basic.json#/definitions/entityName"
    },
    "fullyQualifiedName": {
      "description": "Fully qualified name: service.database.schema.storedProcedure",
      "$ref": "../../type/basic.json#/definitions/fullyQualifiedEntityName"
    },
    "displayName": {
      "description": "Display name",
      "type": "string"
    },
    "description": {
      "description": "Markdown description",
      "$ref": "../../type/basic.json#/definitions/markdown"
    },
    "storedProcedureType": {
      "$ref": "#/definitions/storedProcedureType"
    },
    "storedProcedureLanguage": {
      "$ref": "#/definitions/storedProcedureLanguage"
    },
    "storedProcedureCode": {
      "description": "Source code of the stored procedure",
      "$ref": "../../type/basic.json#/definitions/sqlQuery"
    },
    "parameters": {
      "description": "Input/output parameters",
      "type": "array",
      "items": {
        "$ref": "#/definitions/parameter"
      }
    },
    "returnType": {
      "description": "Return data type",
      "type": "string"
    },
    "databaseSchema": {
      "description": "Parent schema",
      "$ref": "../../type/entityReference.json"
    },
    "database": {
      "description": "Parent database",
      "$ref": "../../type/entityReference.json"
    },
    "service": {
      "description": "Database service",
      "$ref": "../../type/entityReference.json"
    },
    "owner": {
      "description": "Owner (user or team)",
      "$ref": "../../type/entityReference.json"
    },
    "domain": {
      "description": "Data domain",
      "$ref": "../../type/entityReference.json"
    },
    "tags": {
      "description": "Classification tags",
      "type": "array",
      "items": {
        "$ref": "../../type/tagLabel.json"
      }
    },
    "version": {
      "description": "Metadata version",
      "$ref": "../../type/entityHistory.json#/definitions/entityVersion"
    }
  },

  "required": ["id", "name", "databaseSchema"]
}

{
  "@context": {
    "@vocab": "https://open-metadata.org/schema/",
    "om": "https://open-metadata.org/schema/",
    "rdfs": "http://www.w3.org/2000/01/rdf-schema#",
    "xsd": "http://www.w3.org/2001/XMLSchema#",

    "StoredProcedure": "om:StoredProcedure",
    "name": {
      "@id": "om:storedProcedureName",
      "@type": "xsd:string"
    },
    "fullyQualifiedName": {
      "@id": "om:fullyQualifiedName",
      "@type": "xsd:string"
    },
    "displayName": {
      "@id": "om:displayName",
      "@type": "xsd:string"
    },
    "description": {
      "@id": "om:description",
      "@type": "xsd:string"
    },
    "storedProcedureType": {
      "@id": "om:storedProcedureType",
      "@type": "@vocab"
    },
    "storedProcedureLanguage": {
      "@id": "om:storedProcedureLanguage",
      "@type": "@vocab"
    },
    "storedProcedureCode": {
      "@id": "om:storedProcedureCode",
      "@type": "xsd:string"
    },
    "returnType": {
      "@id": "om:returnType",
      "@type": "xsd:string"
    },
    "parameters": {
      "@id": "om:hasParameter",
      "@type": "@id",
      "@container": "@list"
    },
    "databaseSchema": {
      "@id": "om:belongsToSchema",
      "@type": "@id"
    },
    "database": {
      "@id": "om:belongsToDatabase",
      "@type": "@id"
    },
    "service": {
      "@id": "om:belongsToService",
      "@type": "@id"
    },
    "owner": {
      "@id": "om:ownedBy",
      "@type": "@id"
    },
    "domain": {
      "@id": "om:inDomain",
      "@type": "@id"
    },
    "tags": {
      "@id": "om:hasTag",
      "@type": "@id",
      "@container": "@set"
    }
  }
}

{
  "@context": "https://open-metadata.org/context/storedProcedure.jsonld",
  "@type": "StoredProcedure",
  "@id": "https://example.com/data/procedures/calculate_order_total",

  "name": "calculate_order_total",
  "fullyQualifiedName": "postgres_prod.ecommerce.public.calculate_order_total",
  "displayName": "Calculate Order Total",
  "description": "Calculates the total amount for an order including tax and shipping",
  "storedProcedureType": "Function",
  "storedProcedureLanguage": "PL/pgSQL",
  "returnType": "NUMERIC",

  "storedProcedureCode": "CREATE OR REPLACE FUNCTION calculate_order_total(p_order_id BIGINT)\nRETURNS NUMERIC AS $$\nDECLARE\n    v_total NUMERIC;\nBEGIN\n    SELECT SUM(quantity * price) + tax + shipping\n    INTO v_total\n    FROM order_items\n    WHERE order_id = p_order_id;\n    \n    RETURN v_total;\nEND;\n$$ LANGUAGE plpgsql;",

  "parameters": [
    {
      "name": "p_order_id",
      "dataType": "BIGINT",
      "parameterMode": "IN",
      "description": "Order ID to calculate total for"
    }
  ],

  "databaseSchema": {
    "@id": "https://example.com/data/schemas/public",
    "@type": "DatabaseSchema",
    "name": "public"
  },

  "database": {
    "@id": "https://example.com/data/databases/ecommerce",
    "@type": "Database",
    "name": "ecommerce"
  },

  "service": {
    "@id": "https://example.com/services/postgres_prod",
    "@type": "DatabaseService",
    "name": "postgres_prod"
  },

  "owner": {
    "@id": "https://example.com/users/john.smith",
    "@type": "User",
    "name": "john.smith",
    "displayName": "John Smith"
  },

  "domain": {
    "@id": "https://example.com/domains/sales",
    "@type": "Domain",
    "name": "Sales"
  },

  "tags": [
    {
      "@id": "https://open-metadata.org/tags/BusinessLogic/Critical",
      "tagFQN": "BusinessLogic.Critical"
    }
  ]
}