Re: ECPG bug: "unterminated quoted identifier"

> EXEC SQL DO
Thanks, it works!

> Note that if you mean those :i's as references to
> your ECPG variable, I don't think that works in either syntax, since
> it's inside a literal.)
Yes, you are right! I had to use a temporary table as a buffer in order to
refer to host variable values in an anonymous block.

Thank you for helping me!

However, there is one another case when I have to use a C string literal
with nested double quotes inside Embedded SQL.

I have a stored procedure with case sensitive name and INOUT parameter:

CREATE OR REPLACE PROCEDURE embeddedc."My_Proc_outparam"(INOUT p TEXT)
AS
$BODY$
BEGIN
p := '222';
END;
$BODY$
LANGUAGE plpgsql;

When I call it from DB there is no issue:

DO
$BODY$
DECLARE
t TEXT := 'qqq';
BEGIN
CALL embeddedc."My_Proc_outparam"(t);
END;
$BODY$

But when I try to call this procedure from embedded SQL...
int main()
{
EXEC SQL char foo[9];

EXEC SQL CALL embeddedc."My_Proc_outparam"(:foo);

return 0;
}
...an error occurs: "SQL error: too few arguments on line ..."

As far as I can see the Embedded SQL CALL statement does not support
procedures with INOUT parameters.

To get around this limitation, I decided to use PREPARE .. FROM + EXECUTE.

But I ran into the situation that I described in here:
https://www.postgresql.org/message-id/flat/CA%2B4qtLett6CMxojrwn%2ByQq7qmN8SMOM3eZQ8yQG9D5tCuF-vKw%40mail.gmail.com

On Wed, Oct 21, 2020 at 4:12 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 1250kv <1250kv(at)gmail(dot)com> writes:
> > I have come across cases in which there is a need to use nested double
> > quotes inside C string literal.
>
> > EXEC SQL EXECUTE IMMEDIATE "DO $$\n\
> > BEGIN\n\
> > :i := embeddedc.\"My_Func\"(:i);\n\
> > END\n\
> > $$";
>
> I'd be interested to understand why you feel the need to write that,
> and not just
>
> EXEC SQL DO $$
> BEGIN
> :i := embeddedc."My_Func"(:i);
> END
> $$;
>
> AFAICS, EXECUTE IMMEDIATE with a constant string isn't really useful
> for anything. (Note that if you mean those :i's as references to
> your ECPG variable, I don't think that works in either syntax, since
> it's inside a literal.)
>
> regards, tom lane
>