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