[PATCH 4 of 5 saem_ref] [ark] Add PL/pgSQL function to generate qualified ARK identifier

Denis Laxalde denis.laxalde at logilab.fr
Thu Feb 15 15:00:15 CET 2018


Philippe Pepiot a écrit :
> On 14/02/2018, Denis Laxalde wrote:
>> # HG changeset patch
>> # User Denis Laxalde <denis.laxalde at logilab.fr>
>> # Date 1518453568 -3600
>> #      Mon Feb 12 17:39:28 2018 +0100
>> # Node ID b19f5bb5f1a93835b5522d586da88868de31c287
>> # Parent  f584d40d5f5a3caeded786b72d831f068a1ca00d
>> # Available At http://hg.logilab.org/review/cubes/saem_ref
>> #              hg pull http://hg.logilab.org/review/cubes/saem_ref -r b19f5bb5f1a9
>> # EXP-Topic ark/qualifiers
>> [ark] Add PL/pgSQL function to generate qualified ARK identifier
> 
> [...]
> 
>> +CREATE OR REPLACE
>> +FUNCTION gen_qualified_ark(base TEXT, len INTEGER)
>> +RETURNS TEXT AS $$
>> +DECLARE
>> +    ark_qualifier TEXT;
>> +    found INTEGER;
>> +BEGIN
>> +    BEGIN
>> +        SELECT 1 INTO STRICT found FROM ark WHERE ark.name = base AND ark.qualifier = '';
> 
> I think you can use 'PERFORM 1 FROM ark ...; IF NOT FOUND THEN RAISE
> ...' which avoid using a dedicated 'found' variable.
> 

This and your other reply are very good advices, thanks!
Sending a v2 soon.

>> +    EXCEPTION
>> +        WHEN NO_DATA_FOUND THEN
>> +            RAISE 'no ark record matching name "%" found', base
>> +                USING ERRCODE = 'invalid_parameter_value';
>> +    END;
>> +    LOOP
>> +        BEGIN
>> +            INSERT INTO ark
>> +                VALUES (base, gen_ark_qualifier(len))
>> +                RETURNING qualifier INTO ark_qualifier;
>> +            RETURN ark_qualifier;
>> +        EXCEPTION
>> +            WHEN unique_violation THEN
>> +                -- Continue and try with another "name".
>> +                RAISE WARNING 'unique_violation';
>> +                NULL;
>> +        END;
>> +    END LOOP;
>> +END;
> 
> [...]
> 



More information about the saem-devel mailing list