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?
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$;
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 sqldaddy.io
Just to add a third option that I came up with which combines the queries inside a single EXECUTE format(); statement, here's what I came up with:
CREATE OR REPLACE FUNCTION cnms_fy22q2.drop_summary_tables_function(
fyq text)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
BEGIN
-- logic
EXECUTE format(
'TRUNCATE TABLE schema_%s.my_table;
DROP TABLE schema_%s.my_table', fyq, fyq);
END;
$BODY$;