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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
