How can I use if exists with execute?-CodePudding

I am unable to create this stored procedure due to syntax error. Is here any one can solve this syntax issue?

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; $$

CodePudding user response：

exists only works together with a SELECT statement.

But there is no need for an IF statement at all. All three commands support the IF EXISTS option. To include a table name or any identifier in a dynamic SQL using format() you should use the %I placeholder.

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; $$

call remote_db_connection_remover('postgres','remote_server12','student');