Troubleshooting PG Function

Question

I have this function:

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;

I try calling it like so:

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

and I get this error:

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

Why isn't c_user being replaced with my param (user1)?

Answer 1

Why isn't c_user being replaced with my param (user1)?

Because the object names (metadata) cannot be substituted with the variables (data).

Choosing the metadata dynamically is usually not the best design, but if you need to live with it, you should do something along the lines of

EXECUTE 'INSERT INTO ' || c_user || '.AIRSPACE_AVAILABILITY ' …

instead.

See here for more details.

Answer 2

When you write something like this:

INSERT INTO c_user.AIRSPACE_AVAILABILITY

the database assumes that the 'c_user' is a namespace, not a variable. If you want to use the value of the c_user variable in such a query, you should use execute statement:

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