Thank you very much for this explanation/reply. It precisely answers
my question.
Unfortunately, it prompts a new question. I am using 8.4.2 which I
assume is new enough to trigger a "yes" response to "If you have a
version new enough to have synchronize_seqscans...". I have
absolutely no idea how to turn that off. Perhaps the best thing would
be to direct me to the documentation where turning it off is described
so that I can become more autonomous. However, accompanying that with
explicit directions would be welcome too.
I am in Greenwich +1 timezone, but I fear you are in the 2AM time
zone. Thank you again,
John
On Feb 24, 2010, at 8:06 AM, Tom Lane wrote:
John Gage <jsmgage@xxxxxxxxxxxxxx> writes:
This is a two-part question:
1) I have a source_text that I want to divide into smaller subunits
that will be contained in rows in a column in a new table. Is it
absolutely certain that the initial order of the rows in the
resultant
table after this operation:
CREATE TABLE new_table AS SELECT regexp_split_to_table(source_text,
E'regexp') as subunits FROM source_table;
will be the same as the order of these subunits in the original text?
If you have a version new enough to have synchronize_seqscans, you'd
need to turn that off. Otherwise should be OK.
2) I would like to be able to create a serial-type column during
CREATE TABLE AS in the new table that "memorizes" this order so
that I
can reconstruct the original text using ORDER BY on that serial
column. However, I am stumped how to do that.
I think the trick is to get the SRF to be expanded before the serial
values are assigned. There's more than one way to do it, but I think
(too tired to experiment) this would work:
CREATE TABLE new_table (id serial, subunits text);
INSERT INTO new_table(subunits) SELECT
regexp_split_to_table(source_text,
E'regexp') FROM source_table;
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
