Tag: field

I’m having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I’m trying to insert an array of these types via the procedure.

Just to put this out there: this code works without the array of numbers (i.e. if I were to just remove the variable as_ids from the table, composite type, and procedure, the code runs the way its supposed to. for some reason it just doesn’t work with the array).

The table

CREATE TABLE user_ans(
ans_id INT GENERATED ALWAYS AS IDENTITY,
fq_id INT NOT NULL,
p_id INT NOT NULL,
j_id INT NOT NULL,
u_id INT NOT NULL,
d_id INT,
ans TEXT NOT NULL,
as_ids INT() NOT NULL,
created TIMESTAMP,
updated TIMESTAMP
);

The composite type

CREATE TYPE new_ans_obj AS (
    ans TEXT, 
    as_ids INT(),
    created TIMESTAMP,
    d_id INT,
    fq_id INT,
    j_id INT,
    p_id INT,
    u_id INT
);

The procedure

CREATE OR REPLACE PROCEDURE add_ans_arr (
    new_ans new_ans_obj()
)
    LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
    SELECT * from unnest(new_ans);
    COMMIT;
END;$$;

Attempts

Command I use:

call add_ans_arr((ARRAY('(sdf,{2002,2005},2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,{2002,2005},2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'))::new_ans_obj());

Output:

ERROR:  malformed array literal: "{2002"
LINE 1: call add_ans_arr((ARRAY('(sdf,{2002,2005},2021-01-19T21:4...
                                   ^
DETAIL:  Unexpected end of input.

Command I use:

call add_ans_arr((ARRAY('(sdf,(ARRAY(2002,2005))::int(),2021-01-19T21:48:28.997Z,1001,7001,3,6001,1)', '(sdf,(ARRAY(2002,2005))::int(),2021-01-19T21:48:29.626Z,1001,7002,3,6001,1)'))::new_ans_obj());

Output:

ERROR:  malformed array literal: "(ARRAY(2002"
LINE 1: call add_ans_arr((ARRAY('(sdf,(ARRAY(2002,2005))::int(),2021...
                                ^
DETAIL:  Array value must start with "{" or dimension information.

Post obvious attempts

I’ve also tried different variations of the two above commands (using parenthesis, putting the size of the array in, etc), but with similar outputs. I’ve read other posts about inserting arrays into Postgres, as well as the documentation and it seems like I’m using the right syntax (although probably not)?

I was thinking that the array of integers might be conflicting with the ‘unnest’ function, so I rewrote the procedure to following, but still the same problem pops up:

CREATE OR REPLACE PROCEDURE add_ans_arr (
    arr new_ans_obj()
)
    LANGUAGE plpgsql
AS $$
DECLARE
    ans_obj new_ans_obj;
BEGIN
    FOREACH ans_obj IN ARRAY arr
    LOOP
        INSERT INTO user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id)
        VALUES (ans_obj.ans, ans_obj.as_ids, ans_obj.created, ans_obj.d_id, ans_obj.fq_id, ans_obj.j_id, ans_obj.p_id, ans_obj.u_id);
    END LOOP;
    COMMIT;
END;$$;

I’m able to individually insert these objects into the table:

insert into user_ans(ans, as_ids, created, d_id, fq_id, j_id, p_id, u_id) VALUES ('lala', '{2002}', '2021-01-19T21:48:29.626Z', 1001, 7002, 3, 6001, 1);

But I don’t really understand why the above method isn’t working? I’ve tried putting the single quotes into the my first command, but I just get a syntax error. Also I don’t want to mess too much with the

ARRAY('','')::ans_obj()

structure because it works for the rest of my other procedures, and this structure is generated by my server. Although I’m open to change it the server-side code if there’s something horribly wrong with this structure, but again – it works for the rest of my stored procedures.

Any direction/help would be greatly appreciated.