由于語法錯誤，我無法創建此存盤程序。有沒有人可以解決這個語法問題？
DROP PROCEDURE REMOTE_DB_CONNECTION_REMOVER;
CREATE PROCEDURE REMOTE_DB_CONNECTION_REMOVER(
rm_server_user varchar(100),
rm_server_name varchar(100),
rm_table varchar(100),
rm_server_extenstion integer=0
) LANGUAGE PLPGSQL AS $$
BEGIN
IF EXISTS(EXECUTE FORMAT('DROP FOREIGN TABLE %s', rm_table)) THEN
EXECUTE FORMAT('DROP FOREIGN TABLE %s', rm_table);
END IF
IF EXISTS(EXECUTE FORMAT('DROP USER MAPPING FOR %s SERVER %s',rm_server_user, rm_server_name)) THEN
EXECUTE FORMAT('DROP USER MAPPING FOR %s SERVER %s',rm_server_user, rm_server_name);
END IF
IF EXISTS(EXECUTE FORMAT('DROP SERVER %s', rm_server_name)) THEN
EXECUTE FORMAT('DROP SERVER %s', rm_server_name);
END IF
END; $$
uj5u.com熱心網友回復：
exists僅與SELECT陳述句一起使用。
但是根本不需要 IF 陳述句。所有三個命令都支持該IF EXISTS選項。要在動態 SQL 中包含表名或任何識別符號，format()您應該使用%I占位符。
DROP PROCEDURE REMOTE_DB_CONNECTION_REMOVER;
CREATE PROCEDURE REMOTE_DB_CONNECTION_REMOVER(
rm_server_user varchar(100),
rm_server_name varchar(100),
rm_table varchar(100),
rm_server_extension integer=0
) LANGUAGE PLPGSQL AS $$
BEGIN
EXECUTE FORMAT('DROP FOREIGN TABLE IF EXISTS %I', rm_table);
EXECUTE FORMAT('DROP USER MAPPING IF EXISTS FOR %I SERVER %I',rm_server_user, rm_server_name);
EXECUTE FORMAT('DROP SERVER IF EXISTS %I', rm_server_name);
END; $$
呼叫 remote_db_connection_remover('postgres','remote_server12','student');
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/437314.html
標籤：PostgreSQL 存储过程 plpgsql 动态 sql