What Identifier in "EXECUTE FORMAT" with "FOR ... IN" loop "record" variable/iterator that uses dot/period "." for Column name in PLPGSQL Procedure?

user_9796935 0
Viewed 443

I am using a basic plpgsql EXECUTE... FORMAT dynamic script with a FOR...IN loop. The problem is, the LOOP has a variable/pointer (like any FOR LOOP in any language) that iterates through the query result set of the SELECT... query in the EXECUTE. I need to make the column name part after the period/dot in temprecord.column_name dynamic, and thus use an Identifier (i.e. %I, %s, %L) for it, As you know, the way to get data (In this column is an ALTER TABLE sql statement) out of this variable/pointer is to use the "dot notation", that is, i.e.

/* Output employee names from column "names" in Employee table */

FOR temprecord IN
EXECUTE format('SELECT * 
                FROM %I t', ''Employee'')
LOOP
   EXECUTE temprecord.names; -- THIS WORKS FINE WHEN I HARDCODE IT. I CAN'T SEEM TO MAKE IT DYNAMIC
END LOOP;

So the above works fine when I hardcord temprecord.names. The problem is I want the column name dynamic, so if different callers/methods call my function, I can select different columns through the temprecord iterator, and EXECUTE this data.

I tried many times what I have below and the best response I have gotten so far is that the sql query that I have in the column (as I stated above) executed, however, it showed an syntax error and truncation error, but I clearly noticed it was returned the all three columns of my table and concatenating the columns together. But I know the real problem is the variable temprecord not picking up on the dot notation that specifies column name i.e. temprecord.column_name_here, that's why it returns all columns and throws truncation here. As I stated above, it works when hardcoded.

i.e.

/* Using the $$ for string formatting here */

CREATE OR REPLACE PROCEDURE my_proc(drop_or_add text)
    LANGUAGE plpgsql
AS
$procedure$
DECLARE
  temprecord record;
  col_nm text;
BEGIN
  col_nm := concat_ws('_','sql',drop_or_add); -- SHOULD CONCATENATE TO COLUMN NAME, I.E. sql_drop OR sql_add WHICH HOLDS SQL "ALTER TABLE..." QUERY
  
FOR temprecord IN
EXECUTE format($f$ SELECT t.col1, t.col2, t.col3%s 
                   FROM some_tbl t
                   WHERE t.col4 = %L, drop_or_add, 'blue')
LOOP
  EXECUTE format($f$ %I.%s $f$, "temprecord", col_nm);
END LOOP;

END
$procedure$;

Hopefully none of this syntax is confusing, nor my logic. Again, I am simply looping with the temprecord iterator variable and trying to access some columns of my database table, that holds some SQL statements and needs to be dynamic based on the argument passed to my Procedure. So, potentially EXECUTE temprecord.sql_drop or EXECUTE temprecord.sql_add can execute.

I always used the %s in my other plpgsql scripts when I access a column of table using dot notation i.e. tbl1.id = tbl2.% and it's always worked fine. I have tried basically every combination possible, but I'll list a few here, i.e.

EXECUTE format($f$ %I.%s $f$, "temprecord", col_nm);
EXECUTE format($f$ %I%s $f$, "temprecord", '.sql_drop');
EXECUTE format($f$ %I.%s $f$, temprecord, col_nm);
EXECUTE format($f$ %I.%I $f$, "temprecord", col_nm);
EXECUTE format($f$ %I.%I $f$, "temprecord", "col_nm");
.
..
...
execute for-in-loop dynamic-sql plpgsql postgresql
2 Answers

I was able to find a solution, however, I absolutely tried every combination of Identifiers and I could not get this to work when concatenating the record iterator and the column name in the EXECUTE FORMAT block. So my solution was just stick stick a CASE statement before the second EXECUTE... and then directly pass that String variable to the EXECUTE and use only one identifier. It seemed like the problem was any time I was trying to divide/split up the temprecord.column_name inside the FORMAT i.e. EXECUTE FORMAT($f$ %I.$s $f$, temprecord,column_name), it would just not work. However, I am able to do this with regular Tables and Columns in SELECT.. FROM WHERE t1.col1 = t2.%, so I'm not sure if its just different with the record variable because its part of a FOR... IN LOOP or something.

Solution

CASE   
  WHEN drop_or_add ILIKE '%drop%' THEN 
    v_exect_sql := temprow.sql_drop;
  WHEN drop_or_add ILIKE '%add%' THEN
    v_exect_sql := temprow.sql_add; 
  END CASE;
BEGIN

EXECUTE format($f$ %s $f$, v_exect_sql); -- Have to pass the `temprecord.column_name` all as one Identifier or would NOT work for me
user_97969350

  1. the string passed to EXECUTE statement have to be valid SQL command. It cannot be an expression.

  2. The PL/pgSQL is almost static language - there are some dynamic features, but almost it is static typed language. Overusing EXECUTE statement is generally bad idea. It can work, but the code will be very unreadable and not well maintainable.

  3. For dynamic access to record, the best way is (today) using transformation to json:

postgres=# create or replace function fx(nm varchar(64))
returns void as $$
declare
  r record; 
  r2 record;
begin
  for r in execute format('select v as %I from generate_series(1,3) g(v)', nm)
  loop
    raise notice '%', (row_to_json(r))->>nm;
  end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select fx('foo');
NOTICE:  1
NOTICE:  2
NOTICE:  3
┌────┐
│ fx │
╞════╡
│    │
└────┘
(1 row)
user_4066910
Related