Thread: Fault when return strings over 256 characters in PLpgSQL function

I have a PLpgSQL function that returns a string (varchar):  if this string
is over 256 characters long then the last three characters are corrupted:
replaced by the string ' (.'

This is my test function:-

-- Function: testconverttousername(varchar, varchar)
DROP FUNCTION testconverttousername();
CREATE OR REPLACE FUNCTION testconverttousername()
  RETURNS varchar AS
--  RETURNS text AS
'
DECLARE
        userName varchar (1000);  -- have tried without (1000) but makes
no difference
     locn int;
BEGIN
userName := \'12345678\';
userName := userName || userName;  -- 16 chars
userName := userName || userName;  -- 32 chars
userName := userName || userName;  -- 64 chars
userName := userName || userName;  -- 128 chars
userName := userName || userName;  --256 chars
userName := userName || \'88\';  --258 chars
-- userName has now "788" at end replaced with " (." ? Test
this...apparently not...
locn :=  position(\'.\' in userName);
IF locn > 0 THEN
    --userName has now "788" at end replaced with " (." ? Test
this...apparently not...
    userName := \'Has got additional character . put in it \';
    END IF;
RETURN userName;  -- has "7888" at end replaced with " (." so fault is at
the return statement??
END
'
  LANGUAGE 'plpgsql' STABLE;


This function returns a string that should end with '7888' but ends with
'78 (.'.  As far as I can see returning text or varchar makes no
difference, and the corruption occurs at the RETURN statement, not before.
The fault only occurs if the string is over 256 characters.

I am using Postgres 8.0.0 latest release on a Windows 2000 server.
Your help would be appreciated, as this is causing some problems!
Cathy



:.________________
CONFIDENTIALITY : This  e-mail  and  any attachments are confidential and
may be privileged. If  you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.

cathy.hemsley@powerconv.alstom.com wrote:
> I have a PLpgSQL function that returns a string (varchar):  if this string
> is over 256 characters long then the last three characters are corrupted:
> replaced by the string ' (.'

I'm skeptical: there is nothing special about 256 characters as far as
the varchar implementation is concerned, nor is the string ' (.' of any
significance.

Running your function (albeit on Linux) yields:

neilc=# select testconverttousername();

                                                 testconverttousername



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


123456781234567812345678123456781234567812345678123456781234567812345678123456781234567812345678123456781234567812345678123456781234567812345678123456781234567812345678123456781234567812345678123456781234567812345678123456781234567812345678123456781234567888
(1 row)

i.e. what one would expect.

-Neil

It could be a pgAdmin III presentation fault.
I'm using PostgreSQL 8.0.0 on Windows 2k and I've got the same problem
but only when inquiring via pgAdmin, using psql from command line all
goes ok. I think that the resulting string are correct, that's why the
function "position(\'.\' in userName);" doesn't find the dot.

Matteo Brusamolin


cathy.hemsley@powerconv.alstom.com wrote:
> I have a PLpgSQL function that returns a string (varchar):  if this
> string
> is over 256 characters long then the last three characters are
corrupted:
> replaced by the string ' (.'

I'm skeptical: there is nothing special about 256 characters as far as
the varchar implementation is concerned, nor is the string ' (.' of any
significance.

Running your function (albeit on Linux) yields:

neilc=# select testconverttousername();

                                                 testconverttousername


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

123456781234567812345678123456781234567812345678123456781234567812345678
123456781234567812345678123456781234567812345678123456781234567812345678
123456781234567812345678123456781234567812345678123456781234567812345678
123456781234567812345678123456781234567888
(1 row)

i.e. what one would expect.

-Neil

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq