Understanding PLPGSQL basics-CodePudding

I there, I am new to PL/pgSQL, the project I am working on started using it for some data-intensive handling implementations, I am failing to understand some basic foundations, even reviewing the docs again and again

So I have a function that looks like this:

CREATE OR REPLACE FUNCTION sum_stuff_over_value(
       param1 uuid,
       param2 uuid,
       param3 enum
        )
      RETURNS float AS
      $$
      DECLARE
        table_name varchar;
        column_name varchar;
        resolution integer;
        another_table varchar := 'name_of_another_table';
        another_column varchar := 'name_of_another_column';
        sum float;

  BEGIN
    -- Get data from another table fiven a param2 ID
    SELECT * INTO table_name, column_name, esolution
    FROM get_table_and_column_by_Id(param2, param3);

    -- Sum table column over region
    EXECUTE format(
        'SELECT sum(grid_mat.%I * grid_def.%I)
            FROM
                get_uncompact_region($1, $2) region
                INNER JOIN %I grid_mat ON grid_mat.index = region.index
                INNER JOIN %I grid_def ON grid_def.index = region.index;
        ', column_name, another_column, table_name, another_table)
        USING param1, resolution
        INTO sum;
    RETURN sum;
  END;
$$
LANGUAGE plpgsql;

I'd say I fairly understand the very very basic flow, instantiate vars, some of them assigned, etc...

What I am struggling the most is understanding which value holds %I, and how here

INNER JOIN %I grid_mat ON grid_mat.index = region.index
INNER JOIN %I grid_def ON grid_def.index = region.index;

%I is holding (I believe) different values to join different tables

I tried to figure it out by raising notices to print values, but I couldn't make it work. I am trying to add some breakpoints to debug this isolated in the DB but as it's new to me is not being straightforward

Can anyone help me understand what is going on here?

Thanks a lot in advance

CodePudding user response：

You should read the documentation of format. The first %I will be replaced by the value of the second argument of format (column_name), escaped as an identifier, the second %I will be replaced by the value of another_column, and so on.

CodePudding user response：

FROM https://www.postgresql.org/docs/14/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW.
around section: Example 43.1. Quoting Values in Dynamic Queries
quote:

Dynamic SQL statements can also be safely constructed using the format function (see Section 9.4.1). For example:
EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue);

Section 9.4.1 LINK: : https://www.postgresql.org/docs/14/functions-string.html#FUNCTIONS-STRING-FORMAT Quote from Section 9.4.1

type (required) The type of format conversion to use to produce the format specifier's output. The following types are supported:

s formats the argument value as a simple string. A null value is treated as an empty string.

I treats the argument value as an SQL identifier, double-quoting it if necessary. It is an error for the value to be null (equivalent to quote_ident).

L quotes the argument value as an SQL literal. A null value is displayed as the string NULL, without quotes (equivalent to quote_nullable). There have serval examples, quote:

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Result: INSERT INTO "Foo bar" VALUES('O''Reilly')

Finally explain: your EXECUTE format string have 4 %I. Then
1st %I refer to column_name
2nd %I refer to another_column
3rd %I refer to table_name
4th %I refer to another_table