Need help (unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql IMMUTABLE; ")

[Hitesh Varule]

Hi,

Need your help.

we have created a function similar to below:

CREATE OR REPLACE FUNCTION aud_status_to_flag(aud_status character) RETURNS
integer AS $$
DECLARE
auditflag integer;

BEGIN
CASE aud_status
WHEN 'C' THEN
auditflag = 1;
WHEN 'U' THEN
auditflag = 2;
WHEN 'D' THEN
auditflag = 3;
ELSE
CASE
WHEN aud_status SIMILAR TO '[0-9]+' THEN
auditflag = CAST(aud_status as integer);
ELSE
auditflag = 0;
END CASE;
END CASE;
RETURN auditflag;
END;
$$ LANGUAGE plpgsql IMMUTABLE;


If we run the script through INSTALL-SHIELD then we get an error message
like "unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql
IMMUTABLE; "

we did change the function to STABLE but that didn't solve the problem.
Also, we did put everything in single quoted strings but that as well
didn't resolve the issue.


Please can you let me know what should we do to run script successfully
through INSTALLSHIELD?

Appreciate your help.

Thank you,
Hitesh

[Rob Richardson]

That looks to me like an error from PostgreSQL rather than from InstallShield.

When I look at a function that works in my database, I see something like:
CREATE OR REPLACE some_function()
RETURNS integer AS
$BODY$
Begin
return 1;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;

So, I suggest putting something between the two dollar signs. I believe, but I'm not positive, that the text between the dollar signs is merely an identifier. If you don't have that identifier, I can imagine PostgreSQL getting confused.

Good luck!

RobR

P.S. Please keep in mind that this advice may be worth exactly what you have paid for it.

[Oliveiros d'Azevedo Cristina]

You're right, Rob, the text in-between the dollar signs is an identifier.

But, Unless I'm mistaken, nothing prevents the identifier from being empty...

Best,
Oliver
----- Original Message -----
From: Rob Richardson
To: pgsql-***@postgresql.org
Sent: Monday, September 10, 2012 2:24 PM
Subject: Re: [NOVICE] Need help (unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql IMMUTABLE; ")


That looks to me like an error from PostgreSQL rather than from InstallShield.



When I look at a function that works in my database, I see something like:

CREATE OR REPLACE some_function()

RETURNS integer AS

$BODY$

Begin

return 1;

end;

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;



So, I suggest putting something between the two dollar signs. I believe, but I'm not positive, that the text between the dollar signs is merely an identifier. If you don't have that identifier, I can imagine PostgreSQL getting confused.



Good luck!



RobR



P.S. Please keep in mind that this advice may be worth exactly what you have paid for it.