0

I have this code, which is an intent of creating a Lorem Ipsum function to populate data on a data warehouse. I intentionally reduced the number of words for the sake of simplicity.

create or replace function lipsum_word (
    _useInitCap boolean default false
)
returns varchar
immutable language plpgsql as $$
declare
    _word varchar;
    _words constant varchar[] = '{"abbas", "abbatia", "abbatis", "abduco", "abeo", "volatilis:", "volens", "volo", "volpes", "voltur", "volturius", "volubilis", "volubiliter", "voluntarius", "voluntas", "volup", "vulticulus", "vultuosus", "vultur", "vulturius", "vultus", "vulva", "werumensium", "winged", "wreck", "xiphias"}';
begin
    _word = _words[random() * array_length(_words, 1)];

    if _useInitCap then 
        _word = initcap(_word);
    end if;

    return _word;
end
$$;

create or replace function lipsum_sentence (
    _wordCount int default 5,
    _useInitCap boolean default false,
    _useFinalDot boolean default true
)
returns varchar
immutable language plpgsql as $$
declare
    _sentence varchar;
begin
    _sentence = lipsum_word();
    if _useInitCap then 
        _sentence = initcap(_sentence);
    end if;

    for i in 1 .. _wordCount - 1 loop
        _sentence = _sentence || ' ' || lipsum_word();
    end loop;

    if _useFinalDot then
        _sentence = _sentence || '.';
    end if;

    return _sentence;
end
$$;

The code is valid but the lipsum_word() call inside the for-in loop is not working properly, as the returned values are something like

select lipsum_word();
select lipsum_word('true');
select lipsum_sentence();
select lipsum_sentence(8, false, true);

 lipsum_word 
-------------
 volturius
(1 row)

 lipsum_word 
-------------
 Vultus
(1 row)

 lipsum_word 
-------------
 abbatis
(1 row)

             lipsum_sentence              
------------------------------------------
 werumensium winged winged winged winged.
(1 row)

                               lipsum_sentence                               
-----------------------------------------------------------------------------
 werumensium winged winged winged winged winged winged winged winged winged.
(1 row)

                    lipsum_sentence                     
--------------------------------------------------------
 Werumensium winged winged winged winged winged winged.
(1 row)

                        lipsum_sentence                        
---------------------------------------------------------------
 werumensium winged winged winged winged winged winged winged.
(1 row)

slim:watson-dw-v1.0.15 coterobarros$ ./runlipsum 
Password for user postgres: 
 lipsum_word 
-------------
 abbatia
(1 row)

 lipsum_word 
-------------
 Vulturius
(1 row)

                     lipsum_sentence                     
---------------------------------------------------------
 winged voluntarius voluntarius voluntarius voluntarius.
(1 row)

                                       lipsum_sentence                                       
---------------------------------------------------------------------------------------------
 winged voluntarius voluntarius voluntarius voluntarius voluntarius voluntarius voluntarius.
(1 row)

As for the spare calls to lorem_word() the function is working properly. I also tested with a raise notice 'random' inside lorem_word function to learn that the loop is not calling it only once.

Any idea about this behavior?

1 Answer 1

1

Declare lipsum_word as VOLATILE instead of IMMUTABLE. See the docs on function volatility; neither STABLE nor IMMUTABLE will work for functions that can legitimately return different results for identical calls.

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

1 Comment

So, interestingly, for an immutable function, the compiler avoids recalling it and stores the last value for an identical pararmeter set.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.