Обсуждение: Can ARRAY( ... ) generate text[][]?
Hi fellow PostgreSQL hackers! I tried to write an SQL glue function to turn an array of alternating key/value pairs into an array of arrays and got the message ERROR: 42704: could not find array type for data type text[] -- BEGIN CODE -- Here's a simplified example: CREATE OR REPLACE FUNCTION text__(variadic text[]) RETURNS text[][] AS $$ SELECT ARRAY( SELECT ARRAY[ $1[i], $1[i+1] ] FROM generate_series(1, array_upper($1,1), 2) i ) $$ LANGUAGE sql; -- Here's what I really wanted: CREATE OR REPLACE FUNCTION xpath_(text, xml, variadic text[]) RETURNS xml AS $$ SELECT ( xpath($1,$2,ARRAY( SELECT ARRAY[ $3[i], $3[i+1] ] FROM generate_series(1, array_upper($3,1), 2) i ) ) )[1]::xml $$ LANGUAGE sql; -- END CODE Is there any decent way to do this in SQL, or do I need to write it in a procedural language? Thanks, _Greg J. Greg Davidson
On Thu, Jun 10, 2010 at 6:59 PM, J. Greg Davidson <greg@ngender.net> wrote: > Hi fellow PostgreSQL hackers! > > I tried to write an SQL glue function to turn an array > of alternating key/value pairs into an array of arrays > and got the message > > ERROR: 42704: could not find array type for data type text[] I do it like this: create type pair_t as (key text, value text); > -- BEGIN CODE > > -- Here's a simplified example: > > CREATE OR REPLACE > FUNCTION text__(variadic text[]) RETURNS text[][] AS $$ > SELECT ARRAY( > SELECT ARRAY[ $1[i], $1[i+1] ] > FROM generate_series(1, array_upper($1,1), 2) i > ) > $$ LANGUAGE sql; create or replace function pairs(variadic text[]) returns pair_t[] as $$ select array(select ($1[i], $1[i+1])::pair_t FROM generate_series(1, array_upper($1,1), 2) i) $$ language sql immutable; merlin