sql - 函数显示错误 "relation my_table does not exist"

Question

我创建了一个函数来生成发票编号，但是当我这样做时:

select get_generated_kodesj()

它显示错误:

relation "transpending_h" does not exist
LINE 19: ...END END END AS "KODETRANSNEW" FROM transpendi...

这里是我的函数声明:

CREATE FUNCTION get_generated_kodesj()
RETURNS CHAR AS $$
DECLARE kodeSJ CHAR;
BEGIN      
        SELECT
        CASE WHEN MAX(RIGHT("KODETRANS",4)) IS NULL THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'0001')
        ELSE
        CASE WHEN MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1<10 THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'000',
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        ELSE
        CASE WHEN MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1<100 AND MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1>=10 THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'00',
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        ELSE 
        CASE WHEN MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1<1000 AND MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1>=100 THEN
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),'0',
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        ELSE 
        CONCAT('SJ-',EXTRACT(YEAR FROM NOW()),LPAD(CAST(EXTRACT(MONTH from NOW()) AS CHAR), 2 ,'0'),
        MAX(CAST(RIGHT("KODETRANS",4) AS INTEGER))+1)
        END END END END AS "KODETRANSNEW" INTO kodeSJ
        FROM transpending_h
        WHERE SUBSTRING("KODETRANS" FROM 5 FOR 4)=CAST(EXTRACT(YEAR from NOW()) AS CHAR)
        AND SUBSTRING("KODETRANS" FROM 9 FOR 2)=CAST(EXTRACT(MONTH from NOW()) AS CHAR);
    RETURN kodeSJ;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

这可能是什么问题？

Answer 1

你评论了:

but i already created this table, and before i declared this function, i run this query (select something), it works.

您创建了表，但显然不在模式 admin 中，这是您函数的自定义 search_path 中唯一的一个(除了临时模式和隐式pg_目录)。检查:

SELECT * FROM pg_tables WHERE tablename = 'transpending_h';

架构限定函数体中的表，错误应该消失。喜欢:myschema.transpending_h。

相关:

• How does the search_path influence identifier resolution and the "current schema"
• How to check if a table exists in a given schema

旁白

• 你操作的数据类型是char，这是一种误解:

  • Compare varchar with char

  改用文本。

• 如果可以避免，根本不要使用双引号标识符。只是引入了开销和困惑。

  • Are PostgreSQL column names case-sensitive?

• 对于这个简单的 SELECT，您不需要 plpgsql。我建议使用普通 SQL 函数。

仔细观察，您的功能几乎所有都可以改进。基本上，您是在增加每月的序列号。可以简化为:

CREATE FUNCTION get_generated_kodesj()
  RETURNS text AS
$func$
   SELECT to_char(now(), '"SJ-"YYYYMM')
       || COALESCE(to_char(MAX(RIGHT("KODETRANS",4))::int + 1, 'FM0000'), '0001')
   FROM   transpending_h
   WHERE  "KODETRANS" LIKE to_char(now(), '"SJ-"YYYYMM"%"')
                        -- assuming your codes start with "SJ-"; else adapt
$func$  LANGUAGE sql
        SECURITY DEFINER
        SET search_path = admin, pg_temp;

相关:

• Remove blank-padding from to_char() output

但整个方法在多用户环境中仍然存在竞争条件。使用全局 serial 或 IDENTITY 列(结合 date 或 timestamp)而不是尝试创建您的自己的每月连续剧。见:

• Auto increment table column
• Custom SERIAL / autoincrement per group of values

如果您需要每月的序列号，请考虑采用不同的方法来避免多用户环境中的竞争条件:

• How to perform conditional insert based on row count?