Re: [GENERAL] Pgsql problem
Thanks. Hstore works perfectly. pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pgsql problem
Hi. I write function in pgsql. This function needs to execute other functions by name. I do it using loop: declare r record; begin for r in execute 'select ' || $1 || '()' loop end loop; But I can't convert a record to array of text. How to do it ? pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pgsql problem
Hey pasman, 2012/1/12 pasman pasmański pasma...@gmail.com Hi. I write function in pgsql. This function needs to execute other functions by name. I do it using loop: declare r record; begin for r in execute 'select ' || $1 || '()' loop end loop; But I can't convert a record to array of text. How to do it ? Presently, hstore is a most flexible solution in such cases. See http://www.postgresql.org/docs/9.1/static/hstore.html -- // Dmitriy.
Re: [GENERAL] Pgsql problem
On Πεμ 12 Ιαν 2012 12:51:00 pasman pasmański wrote: Hi. I write function in pgsql. This function needs to execute other functions by name. I do it using loop: declare r record; begin for r in execute 'select ' || $1 || '()' loop end loop; But I can't convert a record to array of text. How to do it ? Maybe you should a look at array_agg. It is an aggregate function operating on a set of values and returning the resulting array. http://www.postgresql.org/docs/9.0/interactive/functions-aggregate.html pasman -- Achilleas Mantzios IT DEPT -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgSQL problem: How to split strings into rows
I have a table X with some column K consisting of whitespace-separated words. Is there some SELECT query that will list all these words (for the entire table) so that there's one word per row in the returned table? E.g. If the table X is K - foo bar baz quux frobozz eeny meeny miny moe ...I want the result of this query to be foo bar baz quux frobozz eeny meeny miny moe How can I do this? (I have a slight preference for solutions that will work with version 8.2, but I'm interested in any solution to the problem.) TIA! ~K
Re: [GENERAL] PgSQL problem: How to split strings into rows
Kynn Jones wrote on 21.01.2010 19:49: I have a table X with some column K consisting of whitespace-separated words. Is there some SELECT query that will list all these words (for the entire table) so that there's one word per row in the returned table? E.g. If the table X is K - foo bar baz quux frobozz eeny meeny miny moe ...I want the result of this query to be foo bar baz quux frobozz eeny meeny miny moe How can I do this? (I have a slight preference for solutions that will work with version 8.2, but I'm interested in any solution to the problem.) Don't know if this will work with 8.3: select regexp_split_to_table(k, ' ') from x; Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PgSQL problem: How to split strings into rows
Kynn Jones kyn...@gmail.com wrote:
I have a table X with some column K consisting of whitespace-separated words.
Is there some SELECT query that will list all these words (for the entire
table) so that there's one word per row in the returned table? E.g. If the
table X is
K
-
foo bar baz
quux frobozz
eeny meeny
miny moe
...I want the result of this query to be
foo
bar
baz
quux
frobozz
eeny
meeny
miny
moe
How can I do this? (I have a slight preference for solutions that will work
with version 8.2, but I'm interested in any solution to the problem.)
With 8.4:
test=*# select string_to_array('foo bar bartz', ' ');
string_to_array
-
{foo,bar,bartz}
(1 Zeile)
Zeit: 23,390 ms
test=*# select unnest(string_to_array('foo bar bartz', ' '));
unnest
foo
bar
bartz
(3 Zeilen)
With 8.2:
You have to create a function unnest:
CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;
string_to_array() should work in 8.2 (i'm not really sure, but i think,
8.2 contains this funtion)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PgSQL problem: How to split strings into rows
On Thu, 21 Jan 2010 13:49:45 -0500
Kynn Jones kyn...@gmail.com wrote:
I have a table X with some column K consisting of
whitespace-separated words. Is there some SELECT query that will
list all these words (for the entire table) so that there's one
word per row in the returned table? E.g. If the table X is
K
-
foo bar baz
quux frobozz
eeny meeny
miny moe
...I want the result of this query to be
foo
bar
baz
quux
frobozz
eeny
meeny
miny
moe
http://www.postgresql.org/docs/current/static/functions-array.html
string_to_array
select (string_to_array('tano pino gino', ' '))[i] from
generate_series(1, 3) s(i);
You'd get the idea... to get the length of the array you've
array_length.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
