Re: Converting each item in array to a query result row
От | Gevik Babakhani |
---|---|
Тема | Re: Converting each item in array to a query result row |
Дата | |
Msg-id | 4A1FA537.8080300@xs4all.nl обсуждение исходный текст |
Ответ на | Converting each item in array to a query result row (Postgres User <postgres.developer@gmail.com>) |
Список | pgsql-general |
Postgres User wrote: > Hi, > > I'd writing a query against a function (pg_proc) that contains 2 > fields of an array type. Ideally, I'd like to select 1 row from the > table, but return a query row for each item in the array. > > For example, if one row contains the array {"a", "b", "c"} > I'd like the query to return 3 rows, one for each of these elements. > > Any idea if this is possible? > > Thanks. > No matter how you create your sub query results, you still have to create a sub result record by record. Perhaps the following helps: Note that the input parameter is not an array but a string that looks like an array ------------------------------------------------------------------ create or replace function convert_to_query(p_array varchar) returns setof record as $$ declare result record; begin return query select data.idx[enumerator.counter]::varchar from generate_series(1,array_upper(string_to_array(p_array,','),1)) as enumerator(counter), string_to_array(p_array,',') as data(idx); end; $$ language plpgsql; select result.field1 from convert_to_query('a,c,b,d,e,f') as result(field1 varchar); ----------------------------------------------------------- -- Regards, Gevik
В списке pgsql-general по дате отправления: