bug in 7.4.2, with Handling of Double Quotation Marks

On Wed, 31 Mar 2004, Alexander S wrote:

Bug in 7.4.2.. Concern with handling of Double Quotation Marks (").
Escape of double quotation marks can't solve the problem.

----------
CREATE TABLE public.test
(
my_column varchar NOT NULL,
CONSTRAINT my_primary PRIMARY KEY (my_column)
) WITHOUT OIDS;
-----------
CREATE TABLE public.ref_test
(
my_column varchar[]
) WITH OIDS;
-----------
CREATE FUNCTION public."t-f_ref_test"()
RETURNS trigger AS
'BEGIN
FOR i IN 1..30 LOOP
IF NEW."my_column"[i] not in (select "my_column" from public.test) THEN
RAISE EXCEPTION \'error: my_column[] --> test.my_column \';
END IF;
END LOOP;
RETURN NEW;
END;'
LANGUAGE 'plpgsql' VOLATILE;
----------
CREATE TRIGGER t_ref_test
BEFORE INSERT OR UPDATE
ON public.ref_test
FOR EACH ROW
EXECUTE PROCEDURE public."t-f_ref_test"();
----------
insert into test values (' here \"some_text\" ');
----------
insert into ref_test values (' {here \"some_text\"} ');

But here postgres answers: error: my_column[] --> test.my_column.
So I think this exception should not appear here and this is bug.
Instead I can only insert:
--
insert into ref_test values (' {here \\"some_text\\"} '); -- but
this is not exactly that i want to have.

What is the specific problem you're having, is it needing the double
backslashes on insert? is it the output format backslash escaping
quotation marks?

Alexander S <sasha@in.crimea.ua> writes:

Instead I can only insert:
insert into ref_test values (' {here \\"some_text\\"} '); -- but
this is not exactly that i want to have.

Unfortunately, that is what the syntax of array literals requires you to
say.

You might find it easier to work with the ARRAY[] constructor syntax.

regards, tom lane