SQL function to emulate a generated column not working as expected

Question

I wrote a SQL function that looks like this:

CREATE OR REPLACE FUNCTION bathrooms(structure)
  RETURNS double precision AS
$BODY$
    SELECT coalesce(bathrooms_total, 0) +
        coalesce(bathrooms_full, 0) +
        coalesce(bathrooms_half, 0)*.5 +
        coalesce(bathrooms_three_quarter, 0)*.75
    FROM structure 
$BODY$
  LANGUAGE sql VOLATILE;

And noticed that when using it, I usually got the wrong answer. I.e. it would tell me a row had 5 bathrooms when in fact the row only had 1.5. So I wrote a query to test the function that looked like this:

SELECT coalesce(bathrooms_total, 0) +
    coalesce(bathrooms_full, 0) +
    coalesce(bathrooms_half, 0)*.5 +
    coalesce(bathrooms_three_quarter, 0)*.75,
    bathrooms(structure)
FROM structure

And that returned a very interesting result:

row# real_bathrooms
            func_bathrooms
1.   4      4
2.   2      4
3.   1      4
4.   1.75   4
5.   2.5    4
6.   1.5    4
7.   1.75   4
.
.
.
450. 2.5    4
451. 3.5    4
452. 1.5    1.5!!!!!!
453. 1      1.5
454. 2.75   1.5
.
.

It appears that the function calculates once and then caches that value for around 450 rows, then recalculates. This continues at 900, 1350, and 1800, etc. This was starting to make sense, except everything I've read in the docs and on stack overflow makes me thing that setting the function to volatile should solve this kind of problem. Yet the problem, like the stale data, persists. (I'm sorry, I couldn't help myself.)

As the comments request here is a sample of the data:

sample of the data

Any ideas?

I'm not sure the best way to show sample data in this format, but it's a pretty simple table, each row is a structure, there is the usual id as pkey, and then some string columns like structure_type (i.e. House, Garage, Shed), roof_type, etc, and then integer/double precision columns for things like the bathroom columns above and sqft and year built. — lnhubbell, Sep 02 '15 at 15:41

score 2 · Accepted Answer · edited May 23 '17 at 12:14

Your function does not reference the passed parameter like you seem to think. structure in FROM structure references the underlying table, not the passed parameter.

What you seem to want is a function to emulate a "computed field" or "generated column". Would work like this:

CREATE OR REPLACE FUNCTION bathrooms(structure)
  RETURNS double precision AS
$func$
SELECT coalesce($1.bathrooms_total, 0)
     + coalesce($1.bathrooms_full, 0)
     + coalesce($1.bathrooms_half, 0) * .5
     + coalesce($1.bathrooms_three_quarter, 0) * .75
$func$  LANGUAGE sql;

Call (example):

SELECT *,  pg_temp.bathrooms(s) FROM structure s;

Detailed explanation:

Store common query as column?

score 1 · Answer 2 · answered Sep 02 '15 at 16:18

I think your issue is not what you think it is. This doesn't appear to be a caching issue -- rather it looks like you are querying the contents of the table every time for every record you send to the function.

I suspect this is probably what you intended with your function.

CREATE OR REPLACE FUNCTION bathrooms(bath structure)
  RETURNS double precision AS
$BODY$
BEGIN
  return coalesce(bath.bathrooms_total, 0) +
        coalesce(bath.bathrooms_full, 0) +
        coalesce(bath.bathrooms_half, 0)*.5 +
        coalesce(bath.bathrooms_three_quarter, 0)*.75;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

SQL function to emulate a generated column not working as expected

2 Answers2