Troubleshooting PG Function
- by Grasper
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)?
