CODEWITHSUNDEEP
sqlpostgresqlfunctionparameter-passing
lnhubbell
lnhubbell

Reputation: 3385

SQL function to emulate a generated column not working as expected

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?

Upvotes: 0

Views: 67

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658767

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:

Upvotes: 2

Hambone
Hambone

Reputation: 16397

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;

Upvotes: 1

Related Questions