How can I use if exists with execute?

Question

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

Already try that but showing error >> ERROR: column "postgres" does not exist LINE 1: call REMOTE_DB_CONNECTION_REMOVER(postgres,remote_server10,t... ^ — Sheikh Wasiu Al Hasib, Mar 03 '22 at 19:06
Why an IF-statement? You execute 3 different commands, and when they are successful, you execute them again. I'm pretty sure these commands will fail because the object you want to drop doesn't exist anymore. — Frank Heikens, Mar 03 '22 at 19:27
@SheikhWasiuAlHasib: strings need to be enclosed in single quotes `call ...('postgres', 'remote_server', ...)` — , Mar 03 '22 at 19:56

score 1 · Accepted Answer · edited Mar 03 '22 at 20:43

1

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');

edited Mar 03 '22 at 20:43

Sheikh Wasiu Al Hasib

450
5
18

answered Mar 03 '22 at 20:00

Thanks for answer but It is show this error, issue at postgres user. It said it does not exists for that server>>> postgres=# call remote_db_connection_remover(postgres,remote_server12,student); ERROR: column "postgres" does not exist LINE 1: call remote_db_connection_remover(postgres,remote_server12,s... – Sheikh Wasiu Al Hasib Mar 03 '22 at 20:18
1

@SheikhWasiuAlHasib: string values need to be enclosed in single quotes: `call ...('postgres', 'remote_server12', 'student');` – Mar 03 '22 at 20:19
Oh what a mistake from my end. All done right but sometime silly mistake take my whole day, shame for me. @a_horse_with_no_name Thanks you so much. – Sheikh Wasiu Al Hasib Mar 03 '22 at 20:24

How can I use if exists with execute?

1 Answers1