plpgsql - RETURN concatenation with string of spaces 1

arewehavingfunyet · Apr 22, 2003

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 · Apr 22, 2003

Code:

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

arewehavingfunyet · Apr 22, 2003

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 · Apr 22, 2003

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 · Apr 22, 2003

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

plpgsql - RETURN concatenation with string of spaces 1

arewehavingfunyet

MIS

swampBoogie

Programmer

arewehavingfunyet

MIS

rycamor

Programmer

arewehavingfunyet

MIS

Similar threads

Part and Inventory Search

Sponsor