Thread: [Q] parsing out String array
if I have field declared
myvalue text[][]
insert into vladik (myval)
values
(
'{{"\",A", "\"B"}, {"Y", "Q"}}'
)
What do you guys use in your treasurechest of 'addons'
to successfully parse out the above trickery
and get
and get the 4 strings
",A
"B
Y
Q
from within Postgres stored procedure as well as C++ or other client
code.
It seems to me that it is not possible with any built-in command
available
to easily extract the strings out to a multidimensional array
thank you
--
Vlad P
author of C++ ORM http://github.com/vladp/CppOrm/tree/master
--
http://www.fastmail.fm - One of many happy users:
http://www.fastmail.fm/docs/quotes.html
V S P wrote:
> if I have field declared
> myvalue text[][]
>
> insert into vladik (myval)
> values
> (
> '{{"\",A", "\"B"}, {"Y", "Q"}}'
> )
>
>
> What do you guys use in your treasurechest of 'addons'
> to successfully parse out the above trickery
> and get
>
> and get the 4 strings
> ",A
> "B
> Y
> Q
>
> from within Postgres stored procedure as well as C++ or other client
> code.
>
>
> It seems to me that it is not possible with any built-in command
> available
> to easily extract the strings out to a multidimensional array
Actually its pretty easy.
SELECT myval[i][j]
FROM vladik
CROSS JOIN generate_series(1, array_upper(myval, 1)) i
CROSS JOIN generate_series(1, array_upper(myval, 2)) j
Hello, I am sitting on version 7.4.x and am going to upgrade to version 8.3.x. From all I can read I should have no problem with actual format of the pgdump file (for actual dumping and restoring purposes) but I am having problems with encoding (which I was fairly sure I would). I have searched the web for solutions and one solution given (in one thread where Tom Lane answered) was to set the correct encoding in the version 8.3.x database. However, the default encoding in the version 8.3.x instance is currently UTF8 and I am happy with that (in fact, I would even want it to be UNICODE). The encoding for most of the databases in the version 7.4.x was LATIN1. Is there any way I can ignore the LATIN1 encoding and force the database to accept the UTF8 encoding of the new version 8.3.x instance? I get the below message when I try the psql -f <file> <database> command. psql:aranzo20090812:30: ERROR: encoding LATIN1 does not match server's locale en_US.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. Any help would be appreciated. Archie
arsi@archie.netg.se writes:
> I am sitting on version 7.4.x and am going to upgrade to version 8.3.x.
>> From all I can read I should have no problem with actual format of the
> pgdump file (for actual dumping and restoring purposes) but I am having
> problems with encoding (which I was fairly sure I would). I have searched
> the web for solutions and one solution given (in one thread where Tom
> Lane answered) was to set the correct encoding in the version 8.3.x
> database.
> However, the default encoding in the version 8.3.x instance is currently
> UTF8 and I am happy with that (in fact, I would even want it to be
> UNICODE). The encoding for most of the databases in the version 7.4.x was
> LATIN1. Is there any way I can ignore the LATIN1 encoding and force the
> database to accept the UTF8 encoding of the new version 8.3.x instance?
Sure, you can load a latin1 dump into a utf8 database. However a
pg_dumpall script will try to recreate the databases with their
original encodings. You can either edit the script to adjust the
ENCODING options for the databases, or use pg_dump to dump the
databases one at a time and then load them into hand-created
databases on the receiving end.
regards, tom lane
Thank you very much , your suggestion
helped a lot
But, I have a bit more of a challenge now,
my array is being generated by the 'client' (it is not in the database)
so I am trying to employ your method on
'string'
but running into a problem as I cannot typecast correctly
select V[i][j]
FROM
(select '{{A,B,C},{D,E,F}}') as V
CROSS JOIN generate_series(1, 3) i
CROSS JOIN generate_series(1,2) j
Does not work, because V is not an array (it is a string)
and I do not know where to put the typecast
::text[][]
anywhwere I tried I get syntax error
thank you in advance for help
>
> On Wed, 12 Aug 2009 20:52 -0700, "Scott Bailey" <artacus@comcast.net>
> wrote:
> > V S P wrote:
> > > if I have field declared
> > > myvalue text[][]
> > >
> > > insert into vladik (myval)
> > > values
> > > (
> > > '{{"\",A", "\"B"}, {"Y", "Q"}}'
> > > )
> > >
> > >
> > > What do you guys use in your treasurechest of 'addons'
> > > to successfully parse out the above trickery
> > > and get
> > >
> > > and get the 4 strings
> > > ",A
> > > "B
> > > Y
> > > Q
> > >
> > > from within Postgres stored procedure as well as C++ or other client
> > > code.
> > >
> > >
> > > It seems to me that it is not possible with any built-in command
> > > available
> > > to easily extract the strings out to a multidimensional array
> >
> >
> > Actually its pretty easy.
> >
> > SELECT myval[i][j]
> > FROM vladik
> > CROSS JOIN generate_series(1, array_upper(myval, 1)) i
> > CROSS JOIN generate_series(1, array_upper(myval, 2)) j
> >
--
Vlad P
author of C++ ORM http://github.com/vladp/CppOrm/tree/master
--
http://www.fastmail.fm - Accessible with your email software
or over the web
On Sat, Aug 15, 2009 at 01:41:12PM -0400, V S P wrote:
> but running into a problem as I cannot typecast correctly
>
> select V[i][j]
> FROM
> (select '{{A,B,C},{D,E,F}}') as V
> CROSS JOIN generate_series(1, 3) i
> CROSS JOIN generate_series(1,2) j
>
> Does not work, because V is not an array (it is a string)
> and I do not know where to put the typecast
> ::text[][]
Not sure what you tried, but the following does the "right" thing for
me:
SELECT ('{{A,B,C},{D,E,F}}'::text[])[1][1];
The brackets are needed so that PG doesn't get confused between the type
declaration and the array indexing.
--
Sam http://samason.me.uk/
Thank you for your recommendation
I was able to get this to work
(using PG SQL parser to parse out two dimentional PG array
where the array data came from my client program instead of
a DB value)
select ('{{A,B,C},{D,E,F}}'::text[][])[i][j] from
generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 1)) i
cross join
generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 2)) j
On Sat, 15 Aug 2009 21:17 +0100, "Sam Mason" <sam@samason.me.uk> wrote:
> On Sat, Aug 15, 2009 at 01:41:12PM -0400, V S P wrote:
> > but running into a problem as I cannot typecast correctly
> >
> > select V[i][j]
> > FROM
> > (select '{{A,B,C},{D,E,F}}') as V
> > CROSS JOIN generate_series(1, 3) i
> > CROSS JOIN generate_series(1,2) j
> >
> > Does not work, because V is not an array (it is a string)
> > and I do not know where to put the typecast
> > ::text[][]
>
> Not sure what you tried, but the following does the "right" thing for
> me:
>
> SELECT ('{{A,B,C},{D,E,F}}'::text[])[1][1];
>
> The brackets are needed so that PG doesn't get confused between the type
> declaration and the array indexing.
>
> --
> Sam http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Vlad P
author of C++ ORM http://github.com/vladp/CppOrm/tree/master
--
http://www.fastmail.fm - The professional email service
On Sun, Aug 16, 2009 at 05:55:08AM -0400, toreason@fastmail.fm wrote:
> Thank you for your recommendation
> I was able to get this to work
>
> select ('{{A,B,C},{D,E,F}}'::text[][])[i][j] from
> generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 1)) i
> cross join
> generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 2)) j
I've just noticed that you mentioned using 8.4 previously; there's a new
function called unnest in 8.4 that does the above. In your example it
would be:
SELECT unnest('{{A,B,C},{D,E,F}}'::text[]);
and you'd get back your six rows. It appears to do the correct thing
with arrays of higher dimensionality to me, but I only tried up to five.
--
Sam http://samason.me.uk/