Jeremy Jeremy - 5 months ago 7
SQL Question

casting back to a type when calling a function on a query result (in postgres)

I'm passing the result of a query to a function, the function returns a SETOF or a TABLE (Tried both). The final result is an anonymous record, and I can't seem to cast it back to the type of the function. (or any specific type). I just get multiple rows of anonymous records (That do hold the correct information).

CREATE TYPE some_type AS (
foo_id BIGINT,
row_date DATE
);
CREATE TABLE foo
(
foo_id BIGINT,
foo_range DATERANGE NOT NULL
);
CREATE OR REPLACE function prep (in_foo_id BIGINT
in_foo_range)
RETURNS SETOF some_type AS $$
SELECT in_foo_id as foo_id,
generate_series( lower(in_foo_range), upper(in_foo_range)-1, interval '1d')::date as row_date
$$ LANGUAGE sql IMMUTABLE;

select prep(a.foo_id,a.foo_range) from (select * from foo limit 2) a

Answer

You are getting records because you are indeed asking for it. Tell it you want the fields in the records:

select (prep(a.foo_id,a.foo_range)).* 
from (select * from foo limit 2) a;

or much better with a lateral join:

select p.*
from
    (select * from foo limit 2) a,
    prep(a.foo_id,a.foo_range) p;
Comments