Postgres plpgsql return table and value

Question

In a plpgsql function I'm doing returns table with a few columns to return a table with a few rows. But now I want to also return an individual scalar value along with it. So one individual scalar value, plus several rows. How can I do this?

An example is

select count(*) from exampletable into individidual_scalar_value;
select a, b, c from anothertable into several_rows limit 10;

A function cannot return both a table and a scalar. You have to re-think what you want to accomplish. I might suggest two different functions. — Gordon Linoff
– Gordon Linoff, Commented May 19, 2019 at 11:23
@GordonLinoff Could it return a scalar and json representation of table instead? I tried wrapping select statement in something like to_json but that didn't work but I think there is a way? — user779159
– user779159, Commented May 19, 2019 at 11:41
. . It could return two values as a composite type or a row in a table. This seems like an odd requirement. You should explain more about what you are really trying to do. — Gordon Linoff
– Gordon Linoff, Commented May 19, 2019 at 12:21
The query already exists and is returning several rows (e.g. from a plpgsql returns table or returns some_custom_type). I now want to return an additional scalar value (such as the count of a table). The normal way to do it would be to just add a brand new function to return the scalar, and keep the existing function unchanged. But since these two values (the rows and the new scalar) will always be used together, I'd like to see what options there are to return them from the same function. — user779159
– user779159, Commented May 19, 2019 at 12:46

Tim Biegeleisen · Accepted Answer · 2019-05-19 09:53:29Z

1

You could use a subquery for the count on the first table:

select a, b, c, (select count(*) from exampletable)
from anothertable
into several_rows
limit 10;

But note that in general, using LIMIT without ORDER BY is fairly meaningless, because you are not telling Postgres which 10 records you want to actually select. So, add a sensible ORDER BY clause to the above query for best results.

answered May 19, 2019 at 9:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

4 Comments

user779159 Over a year ago

Does the count(*) get calculated for each row? I would assume it just gets calculated once and copied to each rows but does the explain output or anything else "prove" this?

Tim Biegeleisen Over a year ago

@user779159 Yes, the count subquery is not correlated with the outer query, and therefore should be computed once, and then cached. Running explain should reveal this.

user779159 Over a year ago

When anothertable doesn't return any rows then the scalar value doesn't get returned. Do you know how I could do this another way? Maybe with OUT parameters?

Tim Biegeleisen Over a year ago

This seems like an odd requirement. Why not just let the caller handle the case where no rows get returned?

Collectives™ on Stack Overflow

Postgres plpgsql return table and value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related