CREATE OR REPLACE FUNCTION drop_table(varchar) RETURNS VOID AS $$
DECLARE
tablename ALIAS FOR $1;
counter integer;
BEGIN
--compute the counter variable, then continue:
IF counter > 0 THEN EXECUTE 'DROP TABLE tablename'; --TODO: how to use variable here?
END IF;
END;
$$ LANGUAGE plpgsql;
How can I use the variable tablename inside the EXECUTE statement? Currently the sql will use the name "tablename" instead of the given parameter of the function.
You can use format() in PostgreSQL
As Per Documentation :
Format a string. This function is similar to the C function sprintf; but only the following conversion specifications are recognized: %s interpolates the corresponding argument as a string; %I escapes its argument as an SQL identifier; %L escapes its argument as an SQL literal; %% outputs a literal %. A conversion can reference an explicit parameter position by preceding the conversion specifier with n$, where n is the argument position.
select format('Hello %s','World')
result
format
text
---------------
Hello World
so your function should be :
CREATE OR REPLACE FUNCTION drop_table(varchar) RETURNS VOID AS $$
DECLARE
tablename ALIAS FOR $1;
counter integer;
BEGIN
--compute the counter variable, then continue:
IF counter > 0 THEN
EXECUTE format('DROP TABLE %s',tablename); -- or you can directly give argument here ie format('DROP TABLE %s',$1)
END IF;
END;
$$ LANGUAGE plpgsql;
counter?