CODEWITHSUNDEEP
postgresqlrecord
MSSI
MSSI

Reputation: 194

Call postgresql record's field by name

I have a function that uses RECORD to temporarily store the data. I can use it - it's fine. My problem is that I can't hardcode columns I need to get from the RECORD. I must do it dynamically. Something line:

DECLARE
  r1 RECORD;
  r2 RECORD;
BEGIN
  for r1 in Select column_name 
            from columns_to_process
            where process_now = True
  loop
    for r2 in Select * 
              from my_data_table
              where whatever
    loop

       -----------------------------
       here I must call column by its name that is unknown at design time
       -----------------------------
       ... do something with
       r2.(r1.column_name)

    end loop;
  end loop;
END;

Does anyone know how to do it?

best regards M

Upvotes: 1

Views: 660

Answers (2)

MSSI
MSSI

Reputation: 194

Well, after some time I figured out that I could use temporary table instead of RECORD. Doing so gives me all advantages of using dynamic queries so I can call any column by its name.

DECLARE
  _my_var bigint;
BEGIN

  create temporary table _my_temp_table as
  Select _any, _column, _you, _need
  from _my_table
  where whatever = something;

  execute 'Select ' || _any || ' from _my_temp_table' into _my_var;

  ... do whatever

END;

However I still believe that there should be a way to call records field by it's name.

Upvotes: 1

Belayer
Belayer

Reputation: 14921

There is no need to select the all the qualifying rows and compute the total in a loop. Actually when working with SQL try to drop the word loop for your vocabulary; instead just use sum(column_name) in the select. The issue here is that you do not know what column to sum when the query is written, and all structural components(table names, columns names, operators, etc) must be known before submitting. You cannot use a variable for a structural component - in this case a column name. To do that you must use dynamic sql - i.e. SQL statement built by the process. The following accomplishes that: See example here.

create or replace function sum_something( 
          the_something text                 -- column name 
        , for_id        my_table.id%type     -- my_table.id 
        ) 
   returns numeric
  language plpgsql 
as $$
declare 
    k_query_base constant text := 
        $STMT$ Select sum(%I) from my_table where id = %s; $STMT$; 
    l_query      text;
    l_sum        numeric;      
begin 
    l_query = format(k_query_base, the_something, for_id); 
    raise notice E'Rumming Statememt:\n %',l_query;  -- for prod raise Log
    execute l_query into l_sum; 
    return l_sum;
end;
$$;

Upvotes: 1

Related Questions