create or replace function decrypt_columns(vref_cursor refcursor, tname text,secret_key text, column_names text[])
returns refcursor as
$$
declare namess text;
declare sql text;
declare col text;
declare tableName text:=tname;
begin
sql= 'select string_agg(column_name,'','') from information_schema.columns where table_name =''' || quote_ident(tableName) || ''' and column_name NOT IN (' ||array_to_string(column_names, ',')||')';
RAISE NOTICE 'Value of sql: %', sql;
execute
'select string_agg(column_name,'','') from information_schema.columns where table_name =''' || quote_ident(tableName) || ''' and column_name NOT IN (' ||array_to_string(column_names, ',')||')'
into namess;
RAISE NOTICE 'Value of names: %', namess;
foreach col in array column_names
loop
namess=concat(namess,',','convert_from(decrypt(decode('||tableName||'.'||quote_ident(replace(col, '''', ''))||', ''base64''), '''||secret_key||''', ''aes-ecb''), ''UTF8'') as '||replace(col, '''', '')||'');
RAISE NOTICE 'Value: %', namess;
end loop;
open vref_cursor for execute('SELECT '||namess||' from public.'|| quote_ident(tableName) ||'');
return vref_cursor;
end;
$$
language plpgsql;
SELECT decrypt_columns('vref_cursor','user_data','1HarryPotterAndTheSorcerersStone',array['''roleInSquad''']);
FETCH ALL IN vref_cursor;