sql - PG 功能故障排除

Question

我有这个功能:

CREATE OR REPLACE FUNCTION CREATE_AIRSPACE_AVAILABILITY_RECORD
 (cur_user VARCHAR, start_time VARCHAR, start_date VARCHAR, end_time VARCHAR, end_date VARCHAR, airspace_name VARCHAR)

  RETURNS VOID AS '
  DECLARE
 c_user ALIAS for $1;
BEGIN
   IF start_time IS NULL OR
      start_date IS NULL OR
      end_time IS NULL OR
      end_date IS NULL  THEN
         INSERT INTO c_user.AIRSPACE_AVAILABILITY
            (ASP_AIRSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
         SELECT airspace_name,
                 1,
                 ABP.ABP_START_DT,
                 ABP.ABP_STOP_DT
           FROM ABP 
          WHERE EXISTS
             (SELECT ASP.ASP_AIRSPACE_NM
                FROM AIRSPACE ASP
               WHERE ASP.ASP_AIRSPACE_NM = airspace_name);
   ELSIF start_time IS NOT NULL AND
      start_date IS NOT NULL AND
      end_time IS NOT NULL AND
      end_date IS NOT NULL  THEN
         INSERT INTO c_user.AIRSPACE_AVAILABILITY
            (ASP_AIRSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT)
         SELECT airspace_name,
                 1,
                 TO_DATE(start_date||start_time,''YYMMDDHH24MI''),
                 TO_DATE(end_date||end_time,''YYMMDDHH24MI'')
           FROM DUAL
          WHERE EXISTS
             (SELECT ASP.ASP_AIRSPACE_NM
                FROM c_user.AIRSPACE ASP
               WHERE ASP.ASP_AIRSPACE_NM = airspace_name);
   END IF;

END ;
   ' LANGUAGE plpgsql;

我试着这样调用它:

select * from CREATE_AIRSPACE_AVAILABILITY_RECORD('user1','','','','','');

我得到这个错误:

ERROR: schema "c_user" does not exist
SQL state: 3F000
Context: SQL statement "INSERT INTO c_user.AIRSPACE_AVAILABILITY (ASP_AIRSPACE_NM, ASA_TIME_ID, ASA_START_DT, ASA_END_DT) SELECT  $1 , 1, TO_DATE( $2 || $3 ,'YYMMDDHH24MI'), TO_DATE( $4 || $5 ,'YYMMDDHH24MI') FROM DUAL WHERE EXISTS (SELECT ASP.ASP_AIRSPACE_NM FROM c_user.AIRSPACE ASP WHERE ASP.ASP_AIRSPACE_NM =  $1 )"
PL/pgSQL function "create_airspace_availability_record" line 23 at SQL statement

为什么 c_user 没有被替换为我的参数 (user1)？

Answer 1

当你这样写的时候:

INSERT INTO c_user.AIRSPACE_AVAILABILITY

数据库假定“c_user”是一个命名空间，而不是一个变量。如果你想在这样的查询中使用 c_user 变量的值，你应该使用执行语句:

execute 'INSERT INTO ' || c_user || '.AIRSPACE_AVAILABILITY';