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

Re: Row ordering after CREATE TABLE AS...SELECT regexp_split_to_table(source_text, regexp) AS new_column

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Gage <jsmgage(at)numericable(dot)fr>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Row ordering after CREATE TABLE AS...SELECT regexp_split_to_table(source_text, regexp) AS new_column
Date: 2010-02-24 07:06:42
Message-ID: 28900.1266995202@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
John Gage <jsmgage(at)numericable(dot)fr> 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

In response to

Responses

pgsql-general by date

Next:From: John GageDate: 2010-02-24 07:14:11
Subject: Re: Row ordering after CREATE TABLE AS...SELECT regexp_split_to_table(source_text, regexp) AS new_column
Previous:From: Magnus HaganderDate: 2010-02-24 07:00:54
Subject: Re: Trying to get a C function built with MSVC

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