Troubleshooting PG Function
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
开发者_Go百科 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)?
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';
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.
精彩评论