'How to Run Multiple Dynamic Queries in a PostgreSQL Function

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?

postgresqlfunctiondynamic-sqlvba


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

The best resource is probably VBE's help and similar on-line, though it's not complete, and doesn't go into the details your raise. Also it doesn't document 'hidden' methods which were once intended to be deprecated but never were, some of which are very useful! Some of your questions were well answered in those other threads, but more generally about your points -

Qualifying with VBA is more about 'where' the method is sourced, and then whether or not there is any difference in the actual method, which in most cases there isn't apart from sourcing. Most of those strings functions are both in the Strings module and in the _HiddenInterface Interface (which is faster).

Left$ though is only in the Strings module, as are similar $ functions, and why you don't notice any difference in performance. Str is both in the _HiddenInterface and Conversion module.

If you're sure the unqualified versions do what you want you might think best to not to qualify, and generally that's fine. But if you ever end up with a project with a MISSING reference, unqualified Strings and DateTime functions will blow before your code has a chance to be alerted. Depending on how deployed or if not sure I tend to fully qualify, eg VBA.Strings.Left$, VBA.DateTime.Date. FWIW as a small bonus you'll get the intellisense.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Harvey Dent
Solution 2 gwydion93
Solution 3

Related Questions