dimanche 15 août 2021

Function not being called inside for-in loop in PostgreSQL

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?




Aucun commentaire:

Enregistrer un commentaire