In my Postgres 9.6 database I have the following custom domain and table definition:
create domain lowResData as
float[21];
create table myRawValues (
id text,
myData lowResData,
xAxis lowResData,
primary key(id)
);
The following functions are able to produce the result I want for a single item.
create function getData(_id 'text') returns float[] as $$
select myData
from myRawValues
where id = _id
$$ language sql;
create function getAxis(_id 'text') returns float[] as $$
select xAxis
from myRawValues
where id = _id
$$ language sql;
create function myPlotter(myarray float[], myData float[])
returns table (frequency float, amplitude float) as
$$
select *
from unnest(myarray, myData) as u;
$$ language sql;
select * from myPlotter(getAxis('123'), getData('123'));
I want to do the same for all id
's produced from executing a particular query and end up with a result like this:
create or replace function allIdLowResData() returns setof float[] as
$body$
declare r text;
begin
for r in (select id from myRawValues where /*SOME CONDITION*/)
loop
return next myPlotter(getAxis(r), getData(r));
end loop;
return;
end
$body$
language plpgsql;
Use a
LATERAL
join to combine your set-returning function with the rest of the query. Like:See:
Plus, the declared return type of the function (
RETURNS
) must match what's actually returned.Using a simpler SQL function here. You can do the same with PL/pgSQL, lead with
RETURNS QUERY
in this case.You might be interested in these details about Postgres array definitions, quoted from the manual:
Meaning, your
domain
is currently noise without any effect (aside from complications). To actually enforce 1-dimensional arrays with exactly 21 elements in your table, use aCHECK
constraint. Like:I would also ditch the functions
getData()
andgetAxis()
unless there is more to them.