plpgsql - RETURN concatenation with string of spaces 1

[arewehavingfunyet]

I have not been able to figure out howto return a string of spaces from a postgres PLpgSQL function. For example I have a finalresult field defined as varchar. As I LOOP thru a section of code I set

finalresult := finalresult || '' '' || field1 || '',''|| field2;

and then I issue "RETURN finalresult;" which returns trimmed spacing.

The results I would like are:
field1,field2 field1,field2 field1,field2

but what I get is:
field1,field2 field1,field2 field1,field2

Thanks in advance for the help!

 

[swampBoogie]

finalresult := finalresult || case when finalresult <> '' then
'      ' else '' end  || field1 || '',''|| field2;

 

[arewehavingfunyet]

I tried the case statement as you suggested. I did need to change each single quote to 2 single quotes in that case statement. I still did not get the excess spaces. Is it possible that since I am running it via PGAdmin under windows (versus Unix) that the extra spaces are not being interpreted properly? Can't think of why it wouldn't work. I did put 'aaa' in the middle of the spaces to verify the case statement worked, and it did. But it seems to be trimming down all successive spaces to 1 space.

Any further insight is much appreciated! Thanks

 

[rycamor]

Try going into psql from the command line and querying that function, to see if it is just a PGAdmin problem.

Other than that, try posting the code of your function. Perhaps the problem is in typecasting or elsewhere.

Also, you might be interested in using the lpad and rpad functions, rather than just trying to concatenate spaces.

-------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[arewehavingfunyet]

You were so right, it appears to be pgadmin. I ran the sql in psql and I got my spacing correctly. Sometimes we become so deeply entrenched in the code that we forget where we are! I will definitely look into the rpad and lpad functions as well. Thanks