Обсуждение: BUG #1375: Problem with Dollar qouting functions

The following bug has been logged online:

Bug reference:      1375
Logged by:          Tony Caduto
Email address:      tony.caduto@amsoftwaredesign.com
PostgreSQL version: 8.0
Operating system:   Gentoo Linux
Description:        Problem with Dollar qouting functions
Details:

I restored my 7.4.x database to the latest RC, which has several schemas
each with about 30 to 50 functions.
I pull out the source for one of the functions and build a valid function
structure so I can edit it(in notepad for example), and because this is 8.0
I replace the single qoutes we used to have to use around the function body
with dollar qoutes, specificly $BODY$.

Example:

CREATE or REPLACE FUNCTION system.spsys_user_delete( int4)
RETURNS pg_catalog.void AS
$BODY$
DECLARE
USERID_IN alias for $1;
username varchar;

BEGIN
select user_name from system.system_user_information where user_id =
USERID_IN into username;

update
system.SYSTEM_USER_INFORMATION set deleted = 1 where USER_ID = USERID_IN;

return void;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;



I then run my function in my query editor and all the linefeeds are striped
out, so the next time I open it I get the entire function body back as one
long string with all the EOL markers gone.
Here is the interesting part, if I go though and add new EOL markers by
hitting the enter key, then save it, it works without problem and the EOL
markers do not get striped out.

There appears to be a problem with functions that have been restored from
7.4.x and dollar quoting, after it has been saved once, then the EOL markers
replaced the problem goes away.
The issue does not seem to affect new functions created on 8.0.

Tony Caduto wrote:
> There appears to be a problem with functions that have been restored
> from 7.4.x and dollar quoting, after it has been saved once, then the
> EOL markers replaced the problem goes away.

No, it's a problem with notepad randomly reformatting your files.  Use a
different editor.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter,
It also does it from Borland Delphi and I tightly control the EOL markers.
I use a Advanced Syntax Highlighting memo component/widget from TMS
software.
Delphi has a function called adjustlinebreaks, where I can convert the
text going to the server to have either LF or CRLF and Postgres 8.0
strips them out regardless of what they are.
Like I said before If I reopen the the same function and then manually
replace all the line breaks it works fine with the dollar quoting.  It
also works fine if the function is new, i.e. not restored from a 7.x backup.
It also works fine if I don't use the dollar qouting and do find/replace
to convert all the single qoutes in the body, then single quote the
whole thing, then add the header and the footer, which is how I did it
for 7.x.
With 8.0 and dollar quoting it appeared I would not have to do all that,
but as of right now it does not seem to work exactly as expected, at
least with freshly restored functions from 7.x

I can run a SQL monitor that shows the exact SQL that is being sent to
the server, so I will do that and then
let you know.

Thanks for the response,

Tony Caduto


Peter Eisentraut wrote:

>Tony Caduto wrote:
>
>
>>There appears to be a problem with functions that have been restored
>>from 7.4.x and dollar quoting, after it has been saved once, then the
>>EOL markers replaced the problem goes away.
>>
>>
>
>No, it's a problem with notepad randomly reformatting your files.  Use a
>different editor.
>
>
>