I am analyzing an SQL function that was written by my predecessor, and came across the following line at the end of a for i in 1... loop:
array[i] := '#.' || array[i] I don't understand the query enough yet to provide context, this is one of the puzzle pieces that I am trying to solve.
It is part of this function:
Function to transpose data so that each identifier appears on 1 line.
create or replace function transposeTable( out_table_name varchar, -- Temporary table that will save the output (result). in_table_name varchar, -- Table from which the data will be pulled. in_id_column_name varchar, -- Name of the ID column (SSID). in_cols_to_use_for_transpose varchar[], -- Name of the columns (from in_table_name) to use for the transpose. -- ... Data from these columns will be filled into the new columns -- ... "PrefixA 1", "Prefix B 1", ... "Prefix A 2", "Prefix B 2", ... in_transpose_order_by_column_name varchar, -- The column which will be used to make sure "PrefixA 1", "PrefixB 1", ... -- ... "PrefixZ 1" are referencing the same procedure data. in_prefixes varchar[] -- List of prefixes that will be used in the transposed table -- ... (Procedure , Dosis , ...) ) returns void as $$ declare query varchar; i integer; num_prefixes integer; begin -- Construct a query that will return the input table, along with an extra -- column, containing the "PrefixA 1", "PrefixB 1", ... query := ''; num_prefixes = array_length(in_prefixes, 1); for i in 1..num_prefixes loop if i > 1 then query := query || E' \nUNION ALL \n'; end if; query := query || 'select *, '; query := query || 'concat_ws ('''', ''' || in_prefixes[i] || ''', '; query := query || 'ROW_NUMBER() OVER( '; query := query || 'PARTITION BY ' || colname(in_id_column_name) || ' ORDER BY ' || colname(in_transpose_order_by_column_name); query := query || ')) AS thecols'; query := query || ', concat_ws('''', ''' || i::text || ''', '''') AS theorder '; query := query || 'from ' || in_table_name; in_cols_to_use_for_transpose[i] := '#.' || in_cols_to_use_for_transpose[i]; end loop; -- Use the colpivot function (defined above) to transpose the given query. -- The result will be saved in a temporary table with the same name as out_table_name. perform colpivot(out_table_name, query, array[in_id_column_name], in_prefixes, in_cols_to_use_for_transpose); return; 
#.gets prepended to the i-th element of an array.