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

Philippe Pepiot philippe.pepiot at logilab.fr
Thu Feb 15 13:39:29 CET 2018


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.


> +    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