Call set-returning plpgsql function for each row returned from a query

Question

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'));

result1

I want to do the same for all id's produced from executing a particular query and end up with a result like this:

reslt2

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;

For future reference, please post data as *text* not as image. — Erwin Brandstetter, Dec 14 '17 at 00:23

Erwin Brandstetter · Accepted Answer · 2017-12-14T02:17:11.057

Use a LATERAL join to combine your set-returning function with the rest of the query. Like:

CREATE OR REPLACE FUNCTION allIdLowResData()
  RETURNS TABLE (frequency float, amplitude float, id text) AS
$func$
SELECT p.*, r.id
FROM   myRawValues r
LEFT   JOIN LATERAL myPlotter(r.xAxis, r.myData) p ON true
WHERE  /*SOME CONDITION*/
$func$  LANGUAGE sql;

See:

What is the difference between LATERAL and a subquery in PostgreSQL?

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:

However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

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 a CHECK constraint. Like:

CREATE DOMAIN lowResData AS float[21]  -- "[21]" is just for documentation
CONSTRAINT dim1_elem21 CHECK (array_ndims(VALUE) = 1 AND array_length(VALUE, 1) = 21);

I would also ditch the functions getData() and getAxis() unless there is more to them.

Call set-returning plpgsql function for each row returned from a query

1 Answers1