Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] arrays of varchar and "

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Tim Kane <timk(at)hotgames(dot)com>
Cc: PSQL Interfaces <pgsql-interfaces(at)postgreSQL(dot)org>, pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [INTERFACES] arrays of varchar and "
Date: 2000-02-25 04:48:29
Message-ID: 6297.951454109@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfacespgsql-sql
I wrote:
> Tim Kane <timk(at)hotgames(dot)com> writes:
>> I want to insert a number of array elements here...
>> insert into my_table values ('{"value 1", "value 2", "value 3"}');
>> Fine.
>> What do I do however, when I want to insert an array element that
>> contains a " character?

> Hmm.  It looks like array_in tries to deal with backslashed quotes in
> array elements; not sure why it's not working exactly.

Ah, I see ;-)  The correct expression if you want, say, `value "22"a'
in your middle array item is

insert into my_table values ('{"value 1", "value \\"22\\"a", "value 3"}');

You need double backslashes because one level of backslashing will get
stripped off when the lexer handles the string constant '{...}'.
Then what arrives at the array-datatype input converter will be
	{"value 1", "value \"22\"a", "value 3"}
which accomplishes what you want.

There is actually a bug here: when you write it with zero or one
backslashes, the array input converter is presented with
	{"value 1", "value "22"a", "value 3"}
which by all rights should trigger a "malformed array constant"
error message.  But it fails to do so; instead the `"value "22'
part is just dropped on the floor, and the "a" part is taken as
the second array element value.  The input converter should
complain about this input, I think --- though I'm not quite sure
what it should complain about and what it should accept as an
unquoted array element, as in {123.45, 456.66}.  Comments anyone?

			regards, tom lane

PS: if you want to comment, please reply to the pgsql-sql list,
which seems more appropriate for this issue.

In response to

pgsql-interfaces by date

Next:From: Rini DuttaDate: 2000-02-25 16:47:31
Subject: Using libpq in a multithreaded environment
Previous:From: Tom LaneDate: 2000-02-25 01:03:37
Subject: Re: [INTERFACES] arrays of varchar and "

pgsql-sql by date

Next:From: PostmasterDate: 2000-02-25 05:48:29
Subject: DELIVERY FAILURE: User laszlo_acs (laszlo_acs@cms.state.il.us) not listed in public Name & Address Book
Previous:From: Mark KirkwoodDate: 2000-02-25 03:03:31
Subject: New Optimizer Behaviour In 7.0b1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group