postgresql - SQL 窗口与 PSQL 的差异

Question

我正在运行 PostgreSQL 9.3.5，我发现在 PSQL 中创建函数与在 pgAdmin SQL 窗口中创建函数之间存在差异。我有一个函数，我想通过 PSQL 创建(和部署)。

代码是:

CREATE OR REPLACE FUNCTION sync_division_owner_customer_owner_number()
  RETURNS trigger AS
$BODY$
DECLARE 
    row_count           integer                                     := 0;
    cust_uid            customer.customer_uid%TYPE                  := 0;
    cust_date_last_paid customer_owner_number.date_last_paid%TYPE;
BEGIN
    SELECT COALESCE(customer_uid,0) INTO cust_uid FROM division_order D WHERE D.division_order_uid = NEW.division_order_uid;
    IF cust_uid = 0 THEN
        RAISE EXCEPTION 'customer_uid found for function sync_division_owner_customer_owner_number';
    END IF;
    SELECT count(*) INTO row_count FROM customer_owner_number A WHERE A.customer_uid = cust_uid AND A.owner_number = NEW.owner_number;
    CASE row_count
        WHEN 0 THEN
            INSERT INTO customer_owner_number(owner_number, date_first_paid, date_last_paid, customer_uid, deadfiled) 
                VALUES(NEW.owner_number, NEW.date_last_paid, NEW.date_last_paid, cust_uid, TRUE);
        WHEN 1 THEN
           SELECT A.date_last_paid INTO cust_date_last_paid FROM customer_owner_number A WHERE A.customer_uid = cust_uid AND A.owner_number = NEW.owner_number;
           IF cust_date_last_paid < NEW.date_last_paid THEN
                UPDATE customer_owner_number A SET A.date_last_paid = cust_date_last_paid
                    WHERE A.customer_uid = cust_uid AND A.owner_number = NEW.owner_number;
           END IF;
    END CASE;
    RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

当我使用以下命令运行此文件时:psql -U (user) -d test -f "C:\test\function.sql" 我收到一个错误:

ERROR:  invalid type name "customer.customer_uid%TYPE"

然而，如果我在 SQL 窗口中运行完全相同的代码，我一点问题都没有，它会按应有的方式创建函数。我需要使用 %TYPE 变量，因此无法更改它。

如果有人告诉我我还能做些什么来为 PSQL 修复此问题，我将不胜感激。

Answer 1

如果 customer.customer_uid 对 %TYPE declaration 有效在一个客户中，而不是在另一个客户中，这一定是一个可见性的问题。

要么是你的search_path设置不同或临时表起作用(仅在同一 session 中可见)。您可以通过多种方式防范所有可能的原因。这里有两个:

假设表 customer 位于模式 my_schema 中:

1。架构限定引用:

cust_uid  <b>my_schema.</b>customer.customer_uid%TYPE := 0;

2。 SET 函数范围的search_path:

CREATE OR REPLACE FUNCTION sync_division_owner_customer_owner_number()
  RETURNS void AS
$func$

 ...
$func$  LANGUAGE plpgsql
        <b>SET search_path = my_schema,public,pg_temp;</b>

请注意您的触发功能仍然无法正常工作，因为至少还有一个语法错误:

UPDATE customer_owner_number A SET <strike>A.</strike>date_last_paid = cust_date_last_paid

根据文档:

Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

我没有进一步审核。