CODEWITHSUNDEEP
postgresqlloopsplpgsql
user1963937
user1963937

Reputation: 205

Replacing characters in string during loop in plpgsql

In several languages you can do something like:

  1. Declare a variable of string type
  2. Make a loop from 0 to 4
  3. During each iteration you can select and also set a specific character of the string

In php:

$str = '';
for($i=0;$i<5;$i++){
$str .= $i;
}

In javascript:

var str = '';
for(i=0;i<5;i++){
str += i;
}

In both the cases above the variable "str" will contain the string "01234"

Well, can you do the same in plpgsql

I have seen that there are both text[] and varchar[]. And also a lot of string functions. Maybe rpad could help in accomplishng this?

WHat I have tried is the following:

CREATE OR REPLACE FUNCTION test()
RETURNS varchar[] AS $str$
declare str varchar[];
BEGIN
for x in 0..4
loop
str[x] := '_';
end loop;
return str;
END;
$str$ LANGUAGE plpgsql;

But the result of the call is:

[0:4]={_,_,_,_,_}

What am I doing wrong? Is it possible to do the same thing I do in php/js even in postgres?

Upvotes: 1

Views: 3126

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658012

Of course, there is almost always a more elegant and efficient set-based solution when working with SQL:

SELECT overlay('fooxxxxxxxbar' PLACING string_agg(g::text, '') FROM 4 FOR 7)
FROM   generate_series (1,4) g;

Result:

foo1234bar

Also demonstrating that overlay() can replace any length of string with any other string.

Coming from procedural languages, you have to re-think your approach with a declarative, set-based language like SQL. PL/pgSQL adds procedural elements, but a set-based approach is superior most of the time (when possible), even in PL/pgSQL.

Upvotes: 0

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61626

Just as your php or JS code doesn't use arrays, plpgsql doesn't need or want arrays to build a string in a loop, the || concatenation operator is good enough.

CREATE OR REPLACE FUNCTION test() RETURNS text AS $str$
DECLARE
 str text:='';
 x int;
BEGIN
for x in 0..4
loop
  str := str || chr(ascii('0')+x);
end loop;
return str;
END;
$str$ LANGUAGE plpgsql;

Demo:

 select test();
 test  
-------
 01234

UPDATE following comments:

If str was pre-existing and characters should be replaced in it, use overlay.

CREATE OR REPLACE FUNCTION test(str text) RETURNS text AS $str$
DECLARE
 x int;
BEGIN
for x in 0..4
loop
  str := overlay(str PLACING chr(ascii('0')+x) FROM x+1);
end loop;
return str;
END;
$str$ LANGUAGE plpgsql;

Demo:

select test('abcdefghi');
   test    
-----------
 01234fghi

Upvotes: 2

Related Questions