Splitting text column to multiple rows

Hello

try:

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
unnest
--------
23
2
3
4
(4 rows)

regards
Pavel Stehule

2010/3/28 Andrus <kobruleht2@hot.ee>:

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));

I tried code below. Order of rows in result is different from the order of
elements in string.
How to preserve element order ?

Andrus.

create temp table person2 ( id char(9)) on commit drop;
insert into person2 values ('9'),('8');
CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select * from (select unnest(string_to_array('9,23,8,7,4,5',',')) ) xx,
person2
order by id;

2010/3/28 Andrus <kobruleht2@hot.ee>:

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
 SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));

I tried code below. Order of rows in result is different from the order of
elements in string.
How to preserve element order ?

no it is in same order. generate_series generates indexes from
1,2,3.... so result have to be exactly in same order. You do some
wrong.

Pavel

no it is in same order. generate_series generates indexes from

1,2,3.... so result have to be exactly in same order. You do some
wrong.

In my sample I used joind and projecton this changes order.
How to add order number 1,2,.. to created table ?

Andrus.

2010/3/29 Andrus <kobruleht2@hot.ee>:

no it is in same order. generate_series generates indexes from

1,2,3.... so result have to be exactly in same order. You do some
wrong.

In my sample I used joind and projecton this changes order.
How to add order number 1,2,.. to created table ?

you cannot use join for this task

you can use some trick - using a sequences

http://www.postgresql.org/files/documentation/books/aw_pgsql/node75.html
postgres=# create temp SEQUENCE xx;
CREATE SEQUENCE
Time: 3,496 ms
postgres=# select nextval('xx'), * from gg;
nextval | a | b
---------+----+----
1 | 10 | 33
2 | 55 | 22
(2 rows)

Time: 0,926 ms
postgres=# select * from gg;
a | b
----+----
10 | 33
55 | 22
(2 rows)

regards
Pavel Stehule

Pavel,

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
unnest
--------
23
2
3
4
(4 rows)

Result is wrong: it must contain 5 rows.
How to make this work with with any array size ?

Some lines are long.
How to implement word wrap to new row in 80th position but between words
only ?

Andrus.

2010/3/29 Andrus <kobruleht2@hot.ee>:

Pavel,

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
 SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
unnest
--------
23
2
3
4
(4 rows)

Result is wrong: it must contain 5 rows.
How to make this work with with any array size ?

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) g(i) -- there was
error
$$ LANGUAGE sql;

regards
Pavel

"Andrus" <kobruleht2@hot.ee> writes:

Pavel,

pavel@postgres:5481=# select unnest(string_to_array('23,2,3,4,5',','));
unnest
--------
23
2
3
4
(4 rows)

Result is wrong: it must contain 5 rows.

Surely that's a copy-and-paste mistake? I get 5 rows from this example.

regards, tom lane

Pavel

thank you.
How to add word wrap to this at some column between words ?
For example string

'aaaa bbbb cccc dddd'

if word wrap is at column 12 should produce table with two rows:

aaaa bbbb
cccc dddd

Andrus.

I changed unction name and tried:

CREATE OR REPLACE FUNCTION unnest21(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest21(string_to_array('23,2,3,4,5',','));

In this case it returns only 4 rows.
No idea what is happening.

Andrus.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrus" <kobruleht2@hot.ee>
Cc: "Pavel Stehule" <pavel.stehule@gmail.com>;
<pgsql-general@postgresql.org>
Sent: Monday, March 29, 2010 6:00 PM
Subject: Re: [GENERAL] Splitting text column to multiple rows

This returns 5 rows:

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest(string_to_array('23,2,3,4,5',','));

simply changing name returns 4 rows:

CREATE OR REPLACE FUNCTION unnest21(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest21(string_to_array('23,2,3,4,5',','));

Andrus.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrus" <kobruleht2@hot.ee>
Cc: "Pavel Stehule" <pavel.stehule@gmail.com>;
<pgsql-general@postgresql.org>
Sent: Monday, March 29, 2010 6:00 PM
Subject: Re: [GENERAL] Splitting text column to multiple rows

2010/3/29 Andrus <kobruleht2@hot.ee>:

Pavel

thank you.
How to add word wrap to this at some column between words ?
For example string
'aaaa bbbb cccc  dddd'

if word wrap is at column 12 should produce table with two rows:

aaaa bbbb
cccc  dddd

You can't do it. This working only for one column.

regards
Pavel

"Andrus" <kobruleht2@hot.ee> writes:

I changed unction name and tried:
CREATE OR REPLACE FUNCTION unnest21(anyarray)
RETURNS SETOF anyelement as $$
SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest21(string_to_array('23,2,3,4,5',','));

In this case it returns only 4 rows.
No idea what is happening.

Well, the generate_series call is wrong for this use ...

I think if it appeared to work before it was because the built-in
unnest() function was capturing the call.

regards, tom lane

2010/3/29 Andrus <kobruleht2@hot.ee>:

This returns 5 rows:

CREATE OR REPLACE FUNCTION unnest(anyarray)
RETURNS SETOF anyelement as $$
 SELECT $1[i] FROM generate_series(1,4) g(i) <<--- 4 is constant !!!!!!!!!!!!
$$ LANGUAGE sql;

select unnest(string_to_array('23,2,3,4,5',','));

simply changing name returns 4 rows:

sure .. original buggy function is here still.

Pavel

2010/3/29 Tom Lane <tgl@sss.pgh.pa.us>:

"Andrus" <kobruleht2@hot.ee> writes:

I changed unction name and tried:
CREATE OR REPLACE FUNCTION unnest21(anyarray)
RETURNS SETOF anyelement as $$
  SELECT $1[i] FROM generate_series(1,4) g(i)
$$ LANGUAGE sql;

select unnest21(string_to_array('23,2,3,4,5',','));

In this case it returns only 4 rows.
No idea what is happening.

Well, the generate_series call is wrong for this use ...

I think if it appeared to work before it was because the built-in
unnest() function was capturing the call.

he uses 8.1. the bug is in "generate_series(1,>>>4<<<)"

Pavel

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/3/29 Tom Lane <tgl@sss.pgh.pa.us>:

I think if it appeared to work before it was because the built-in
unnest() function was capturing the call.

he uses 8.1. the bug is in "generate_series(1,>>>4<<<)"

If renaming the function makes it appear to work differently,
then there is another function of similar name in there somewhere.
I'm betting the server is not 8.1 after all.

regards, tom lane

If renaming the function makes it appear to work differently,
then there is another function of similar name in there somewhere.
I'm betting the server is not 8.1 after all.

I'm using

"PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit"

Andrus.

2010/3/29 Andrus <kobruleht2@hot.ee>:

If renaming the function makes it appear to work differently,
then there is another function of similar name in there somewhere.
I'm betting the server is not 8.1 after all.

I'm using

"PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit"

oh sorry, you are asked on 8.1 on yesterday

then you don't need custom unnest function.

regards
Pavel Stehule

oh sorry, you are asked on 8.1 on yesterday

I'm developing in 8.4 but customers have servers starting at 8.1
So I asked for a solution starting at 8.1
Hopefully renaming unnest to something other will work in all servers.

Andrus.