PL/pgSQL函数在Supabase REST API调用中失效问题排查

核心问题 #

在Golang应用中通过Supabase RPC调用place_order函数时，函数内的SELECT语句无法匹配到对应数据，抛出「找不到商品或套餐」的异常；但在Supabase SQL Editor中直接执行该函数，或单独提取SELECT语句使用相同参数执行，均能正常返回结果。

函数抛出异常前的代码 #

CREATE
OR REPLACE FUNCTION place_order (
  client_id integer,
  consumer_phone text,
  order_items order_item[]
) RETURNS order_result AS $body$
declare
  i integer;
  order_id integer;
  item order_item;
  current_product integer;
  is_menu boolean;
  temp_menu_product menus_products%rowtype;
  total_price double precision := 0.0;
  product_price double precision := 0.0;
  extra_price double precision := 0.0;
BEGIN
  --CREATE ORDER HEAD
  insert into orders values (default, client_id, consumer_phone, default, 'new')
  returning id into order_id;

  IF order_id <= 0 THEN
    RAISE EXCEPTION 'Error while creating the order for client: % and consumer: %', client_id, consumer_phone;
  END IF;

  FOREACH item in ARRAY order_items LOOP
    
    item.item_name := item.item_name::text;
    item.quantity := item.quantity::integer;
    item.size := item.size::size;
    item.extras := item.extras::text[];

    --Reset loop variables
    current_product := 0;
    is_menu := false;

RAISE LOG 'Before product lookup - item_name: %, quantity: %, size: %', item.item_name, item.quantity, item.size;

    --Find the item in our products or menus
    SELECT id, menu, price
      INTO current_product, is_menu, product_price
      FROM (SELECT id, false as menu, price
              FROM products
            WHERE TRIM(UPPER(name)) = TRIM(UPPER(item.item_name))
              AND size = item.size
              AND valid_from <= NOW()
              AND valid_to >= NOW()
            union all
              SELECT id, true as menu, price
              FROM menus
              WHERE TRIM(UPPER(name)) = TRIM(UPPER(item.item_name))
                AND size = item.size
                AND valid_from <= NOW()
                AND valid_to >= NOW()) as product
    LIMIT 1;

RAISE LOG 'After product lookup - current_product: %, is_menu: %, product_price: %', current_product, is_menu, product_price;

    IF current_product IS NULL then
      RAISE exception 'Error: Cannot find a product or menu for order_item: %', item.item_name;
    END IF;

自定义类型定义 #

CREATE TYPE order_item AS (
  item_name text,
  quantity integer,
  size size,
  extras text[]
);

create type size as enum (
  'S',
  'M',
  'L',
  'XL',
  'XXL'
);

SQL Editor中正常执行的调用示例 #

SELECT
place_order (
1,
'+491605293532',
ARRAY[
ROW ('Pommes', 1, 'M', ARRAY['Mayo'])::order_item
]
);

单独执行SELECT语句的示例 #

SELECT
  id,
  menu,
  price
FROM
  (
    SELECT
      id,
      false as menu,
      price
    FROM
      products
    WHERE
      TRIM(UPPER(name)) = TRIM(UPPER('Pommes'))
      AND size = 'M'
      AND valid_from <= NOW()
      AND valid_to >= NOW()
    union all
    SELECT
      id,
      true as menu,
      price
    FROM
      menus
    WHERE
      TRIM(UPPER(name)) = TRIM(UPPER('Pommes'))
      AND size = 'M'
      AND valid_from <= NOW()
      AND valid_to >= NOW()
  ) as product
LIMIT
  1;

REST API请求示例 #

POST rest/v1/rpc/place_order

JSON请求体：

{
  "client_id" : 1,
  "consumer_phone" : "+491605293532",
  "order_items" : [ {
    "item_name" : "Pommes",
    "quantity" : 1,
    "size" : "M",
    "extras" : [ "Mayo" ]
  }]
}

返回的错误信息 #

{"code": "P0001", "details": null, "hint": null, "message": "Error: Cannot find a product or menu for order_item: Pommes" }

关键现象 #

函数日志显示，SELECT执行前后传入的参数完全符合预期，但查询结果始终为空。

内容的提问来源于stack exchange，提问作者Sebi