How to Run Multiple Dynamic Queries in a PostgreSQL Function-CodePudding

I am having some issues figuring out how to run multiple dynamic queries in a single function.

 CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
BEGIN
 -- logic
    TRUNCATE TABLE 'schema_' || fyq || '.my_table'
    DROP TABLE 'schema_' || fyq || '.my_table';
END;
$BODY$;

I am generally getting syntax errors, like ERROR: syntax error at or near .... What am I doing wrong here?

CodePudding user response：

You can't simply concatenate strings to make a dynamic sql statement. Take a look at EXECUTE and EXECUTE IMMEDIATE.

In your case, you could use it like this:

CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
BEGIN
    -- logic
    EXECUTE 'TRUNCATE TABLE schema_' || fyq || '.my_table';
    EXECUTE 'DROP TABLE schema_' || fyq || '.my_table';
END
$BODY$;

CodePudding user response：

Use the format function which will return a string and execute in your function.

create function permanently_delete_table(fyq text) RETURNS void
    LANGUAGE plpgsql AS $$
    declare
    begin
         EXECUTE format('TRUNCATE TABLE schema_%s.my_table',fyq); 
         EXECUTE format('DROP TABLE schema_%s.my_table',fyq); 
    end
$$;

Demo in DBfiddle