RE: ERROR: unterminated dollar-quoted string at or near "$$"

Hi,

Thank you for your response and time. I will look at the general list you mentioned.
Yes I am having challenge with PSQL.exe on windows, I am sending SQL's as file using -f parameter in PSQL.exe.

SQL file contain triggers, function codes with SS or $BODY$.

Regards,
Gautham.R

-----Original Message-----
From: Francisco Olarte <folarte(at)peoplecall(dot)com>
Sent: Thursday, August 4, 2022 3:57 PM
To: Ravulapati, Gautham <Gautham(dot)Ravulapati(at)ncr(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org; pgsql-generallists.postgresql.org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: ERROR: unterminated dollar-quoted string at or near "$$"

*External Message* - Use caution before opening links or attachments

NOTE: CCing to general.

Hi,

On Thu, 4 Aug 2022 at 10:22, Ravulapati, Gautham <Gautham(dot)Ravulapati(at)ncr(dot)com> wrote:
> I am trying to create functions and triggers from PSQLl on windows.
> Same SQL scripts work from Linux PSQL but fail with following error
> ERROR: unterminated dollar-quoted string at or near "$$"

Let's go looking for the usual suspects.

When you say PSQLI on windows, are you referring to the "psql"( I assume it's .exe ) from standard postgres ?
When you say Linux PSQL are you referring to "psql" from postgres ?

I ask this because Linux is case sensitive, while windows is case preserving, and you refer to
> I see that PSQL version 7 .6 is same across all the postgres version on windows. Is there no latest version of PSQL .
without the "I" later. Answer is probably yes/yes, but lets rule it out.

How are you feeding the scripts to said programs? -f psql switch?
input redirection from the shell? -c and inline?

This is important because in linux shells have regular, although sometimes complex, quoting behaviours, but in windows quoting is different and done by the programs.

> One of the workaround I found was to substitute $$ with single quote and all single quote with double quotes, in my case I cannot do this for two reasons.

This leads me to think you are doing something inline, as in sql you cannot change quotes ( single quotes are for strings, double are for identifiers ).

> Same scripts need to execute on linux and windows These SQl files are
> generated one and would completely new implementation for replacing the $$.

Can you share some part of the scripts? Can you check which kind of line endings you are using ?( I doubt it is the laste one because that will fail normally, if anywhere, on linux, due to extra \015 ).

> Kindly let me know if there are any options at PSQL level or any recommendations.

Last, but not least. This does not seem like a pg bug, but like an operator error. I've replied here once, but will not reply to any followup directed to the bug list, you should try this things in the general list, and post here ( after reading the bug posting guidelines ). Many times pilot errors in the bug list get just discarded, as knowledgeable people here are busy solving bugs.

If you read the headers you'll see I have added the general list, if you replay I strongly recommend you zap the bugs list before replying to all.

Francisco Olarte.