Re: Converting each item in array to a query result row

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