CODEWITHSUNDEEP
postgresql
dang
dang

Reputation: 2412

PostgresSQL Function takes long time

I have a PostgresSQL function:

CREATE OR REPLACE FUNCTION public.replace_id_special_char(my_suf text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
                    declare 
                        c1          text;
                        c2          text;
                        cursor1     refcursor;      
                        lc_time     time with time zone;
                    begin                       
                            open cursor1 for execute 'SELECT column_name,data_type from information_schema.columns WHERE column_name not in(''qr_id'',''qr_nm'',''qr_op_id'',''qr_op_nm'',''qr_op_line_seq'',''date_start'',''date_end'',''qr_op_val_seq'',''qr_op_value'') AND table_name = lower(''tmp_qw_'||$1||''') AND table_schema =current_schema()';                                                
                            loop        
                                fetch cursor1 into c1,c2;
                                exit when not found;
                                raise notice 'column_name: %', c1;
                                if upper(c2)=upper('text') then

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id1__'',''!'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id2__'',''@'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id3__'',''#'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id4__'',''$'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id5__'',''%'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id6__'',''^'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id7__'',''&'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id8__'',''*'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id9__'',''('')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id10__'','')'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id11__'',''-'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id12__'',''='')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id13__'',''+'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id14__'',''"'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id15__'',''\'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id16__'',''.'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id17__'',''/'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id18__'','' '')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id19__'','':'')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id20__'','''')';

                                    execute'update tmp_qw_'||$1||' set '||c1||'=replace('||c1||',''__id0__'','''')';
                                end if;
                            end loop;
                            close cursor1;  
                            return 'values replaced.';  
                    end; $BODY$;

I am running over 27 queries. This function is taking a lot of time to run. Is there a way where I can speed it up?

Upvotes: 0

Views: 80

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246288

Rather than running 27 large UPDATEs, run a single one that does

UPDATE ...
SET ... = replace(
             ...
             replace(
                replace(
                   ..., '__id1__','!'
                ), '__id2__', '@'
             ), ...
          )
FROM ...
WHERE ... LIKE '%\_\_id_\_\_%';

Besides, your function is vulnerable to SQL injection. Use format() to correctly format identifiers and string literals in query strings:

EXECUTE format(
           'update %I set %I = ...',
           'tmp_qw_' || $1,
           c1
        );

Upvotes: 4

Related Questions