Create Instance of User-Defined Type

Question

I'm working on a template script I can give to users and the only thing I want them to touch is in the DECLARE block. The problem is, in the body of the script, I need to perform an operation a variable number of times (in a FOR LOOP). I've created a user-defined TYPE and I'm trying to declare an ARRAY of that type. I think I just don't know the syntax correctly and I've spent a good bit of time searching how to instantiate a variable (or array element) of a user-defined type. The script is several hundred lines long and operates on a handful of variable declarations, but as a simplified example (with comments added inline):

CREATE TYPE t_mine AS
(
    key   varchar(50),
    value text
);

DO
$$
    DECLARE
        setting       t_mine;
        settings      t_mine[] := ARRAY [
            -- This syntax is obviously wrong, I've tried others
            -- with varying results, but all failures
            ('Some', 'Value'),
            ('Another', 'One')
        ];
        error_context text;
    BEGIN
        -- Based on Laurenz' answer, the fix is to change the FOR line to
        -- FOREACH setting IN ARRAY settings
        FOR setting IN SELECT settings
            LOOP
                -- This outputs: Key: {"(Some,Value)"}
                RAISE NOTICE 'Key: %', setting."key";

                -- This outputs: Value: <NULL>
                RAISE NOTICE 'Value: %', setting."value";

                -- Exception raised inside some_operation because
                -- the key is invalid.
                PERFORM some_operation(setting."key", setting."value");
            END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS error_context = PG_EXCEPTION_CONTEXT;
            RAISE INFO 'Failure';
            RAISE INFO 'Error Name:%',SQLERRM;
            RAISE INFO 'Error State:%', SQLSTATE;
            RAISE INFO 'Error Context:%', error_context;
            ROLLBACK;
    END
$$ LANGUAGE plpgsql;

The cleaned up log output:

[00000] Key: {"(Some,Value)"}
[00000] Value: <NULL>
[00000] Failure
[00000] Error Name:Failed to find the key.
[00000] Error State:P0001
[00000] Error Context:PL/pgSQL function some_operation(character varying,text) line 12 at RAISE
SQL statement "SELECT some_operation(setting."key", setting."value")"
PL/pgSQL function inline_code_block line 124 at PERFORM
completed in 7 ms

Can someone please tell me how to correctly instantiate the user-defined type instances within the array declaration? I need to keep this simple for the users to add/remove/change values and if it's going to be too complex I might as well just have them add/remove/change the PERFORM some_operation() calls in the body of the script.

The script calls a bunch of other functions based on logic determined by the variables, but if I can get this working the next step will be to create a new function and the body of this template will simply pass the variables into it.

Laurenz Albe · Accepted Answer · 2021-04-28 12:49:30Z

1

Your declaration and the assignment there are perfect. The problem is in the loop:

To loop through an array, use

FOREACH setting IN ARRAY settings LOOP

edited Apr 28, 2021 at 12:49

answered Apr 28, 2021 at 2:27

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

2 Comments

Dewey Vozel Over a year ago

Thank you Laurenz. I'm not sure what to do with the first bullet point but the FOREACH setting IN ARRAY settings instead of FOR setting IN SELECT settings solved the issue!

Laurenz Albe Over a year ago

Oh, I must have misread your question. Sorry.

Collectives™ on Stack Overflow

Create Instance of User-Defined Type

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related