Обсуждение: 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. > > >