Postgresql, function returns query by calling another function

Question

Postgresql 12. Want a function to return query by calling another function but don't know how to call.

create or replace function getFromA()
returns table(_id bigint, _name varchar) as $$
begin
    RETURN QUERY SELECT id, name from groups;
end; $$ language plpgsql;

create or replace function getFromB()
returns table(_id bigint, _name varchar) as $$
begin
    return query select getFromA();
end; $$ language plpgsql;

select getFromB();

gets error:

SQL Error [42804]: ERROR: structure of query does not match function result type
Detail: Returned type record does not match expected type bigint in column 1.
Where: PL/pgSQL function getfromb() line 3 at RETURN QUERY

How to fix this?

GMB · Accepted Answer · 2020-09-07 20:02:09Z

4

The problem is in getFromB():

return query select getFromA();

Unlike some other databases, Postgres allows set-returning functions directly in the select clause. This works, but can be tricky: this returns a set, hence not the expected structure.

You would need to select ... from getFromA() instead: this way it returns the proper data structure.

create or replace function getFromB()
returns table(_id bigint, _name varchar) as $$
begin
    return query select * from getFromA();
end; $$ language plpgsql;

Demo on DB Fiddle

answered Sep 7, 2020 at 20:02

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql, function returns query by calling another function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related