Error when pasting function blocks into psql

Hi Ludwig:

On Fri, 8 Jul 2022 at 12:37, Ludwig Isaac Lim <ludz_lim@yahoo.com> wrote:

I noticed that I'm getting errors when copy and pasting code for stored procedure from Windows directly into psql running in putty.
To reproduce create a procedure that has multiple statements with each statements having multiple lines, similar to the ones below:

...

Copy and paste from Windows editor (or IDE) to a psql running in a Putty. Chances are it will complain of syntax error. The error doesn't appear when I copy and paste it to an empty file in vim, and the run:
psql -f <file name>
Is there a way of prevent this kind of error when pasting directly into psql? I'm using the latest version of PostgreSQL (server and client. Version 14.4)

You omitted the OS version which is running psql/vim, I assume it is
some king of *ix, probably linux, and which kind of paste you are
using in putty, IIRC it has a couple.

Probably you are sending extra spaces or \015 or something like that
which vim filters for you on save. You can probably confirm these by
pasting your lines into "cat > file" ( remember EOF ing after paste,
enter+^D is the usual way ) and trying to feed that file to psql.

Not having used windows or vim in 20 years I can only think on one
thing to help you with these kind of problems. You'll probably have od
(octal dump) installed. Try pasting the code to 'od -tx1 -tc' which
will give you a nice dump from where you can see if your
IDE/EDITOR/putty is plain nasty tricks ( if you tried pasting into cat
you can cat that file and the one from vim ).

Francisco Olarte.

Ludwig Isaac Lim <ludz_lim@yahoo.com> writes:

Hello:
I noticed that I'm getting errors when copy and pasting code for stored procedure from Windows directly into psql running in putty.
To reproduce create a procedure that has multiple statements with each statements having multiple lines, similar to the ones below:
CREATE OR REPLACE PROCEDURE test()AS$$     UPDATE                     table1      SET                   field1 = ?;

       -- more SQL statements$$LANGUAGE SQL;
Copy and paste from Windows editor (or IDE) to a psql running in a Putty. Chances are it will complain of syntax error. The error doesn't appear when I copy and paste it to an empty file in vim, and the run:
psql -f <file name>
Is there a way of prevent this kind of error when pasting directly into psql?

A gotcha I frequently run into is that if there are any literal tab
characters in the text, pasting it into psql fails because the tabs
invoke tab-completion, producing either no text or unwanted text.
I don't know if that's an issue on Windows (does your psql build
do tab completion?), but if so, the workaround is to disable tab
completion by starting psql with the '-n' switch.

Another fairly likely possibility is that your paste contains
invisible characters that psql does not think are whitespace,
like non-break space (&nbsp;). However, pasting into a file
wouldn't make that problem go away, so the tab idea seems
a shade more likely.

regards, tom lane

On Friday, July 8, 2022 at 10:20:52 PM GMT+8, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Ludwig Isaac Lim <ludz_lim@yahoo.com> writes:

Hello:
I noticed that I'm getting errors when copy and pasting code for stored procedure from Windows directly into psql running in putty.
To reproduce create a procedure that has multiple statements with each statements having multiple lines, similar to the ones below:
CREATE OR REPLACE PROCEDURE test()AS$$     UPDATE                     table1      SET                   field1 = ?;

        -- more SQL statements$$LANGUAGE SQL;
Copy and paste from Windows editor (or IDE) to a psql running in a Putty. Chances are it will complain of syntax error. The error doesn't appear when I copy and paste it to an empty file in vim, and the run:
psql -f <file name>
Is there a way of prevent this kind of error when pasting directly into psql?

A gotcha I frequently run into is that if there are any literal tab
characters in the text, pasting it into psql fails because the tabs
invoke tab-completion, producing either no text or unwanted text.
I don't know if that's an issue on Windows (does your psql build
do tab completion?), but if so, the workaround is to disable tab
completion by starting psql with the '-n' switch.

Another fairly likely possibility is that your paste contains
invisible characters that psql does not think are whitespace,
like non-break space (&nbsp;).  However, pasting into a file
wouldn't make that problem go away, so the tab idea seems
a shade more likely.

            regards, tom lane

I indent using tabs, so I believe this is a more likely explanation. I'll try to use the -n switch and see if the error still occurs.

Thank you,

Ludwig Lim