Constructing array literal as input for PL/pgSQL function

Question

How should an array with elements of a composite type be included in a function call?

After consulting the resources online and attempting the different variants, I continue to get parser errors. Below I've included the types, tables, functions, and execution attempts.

Type

CREATE TYPE jobs_v0.insertable_program AS (
       handle text,
       zip bytea
);

Tables

CREATE TABLE jobs_v0.jobs (
       id bigserial PRIMARY KEY NOT NULL,
       manifest_cid text NOT NULL,
       created_at timestamp NOT NULL DEFAULT now()
);

CREATE TABLE jobs_v0.programs (
       id bigserial PRIMARY KEY NOT NULL,
       job bigserial REFERENCES jobs_v0.jobs(id) NOT NULL,
       handle text NOT NULL,
       package bytea NOT NULL
);

Function:

CREATE OR REPLACE FUNCTION jobs_v0.insert_job(
       manifest_cid text,
       programs jobs_v0.insertable_program[]
) RETURNS void AS
$$
DECLARE
       job_id jobs_v0.jobs.id%TYPE;
       program jobs_v0.insertable_program;
       inserted_programs jobs_v0.programs[];
BEGIN
       -- Insert job
       INSERT INTO jobs_v0.jobs(manifest_cid) VALUES (manifest_cid)
       RETURNING id INTO job_id;

       -- Insert programs
       INSERT INTO jobs_v0.programs(job, handle, package)
       SELECT job_id, * FROM unnest(programs)
       RETURNING * INTO inserted_programs;
END;
$$ LANGUAGE plpgsql;

Execution attempts and errors:

select jobs_v0.insert_job('QmTXzATwNfgGVukV1fX2T6xw9f6LAYRVWpsdXyRWzUR2H9', '{"(main, 0xdeadbeef)"}'::jobs_v0.insertable_program[]);
ERROR:  malformed array literal: "12"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  PL/pgSQL function jobs_v0.insert_job(text,jobs_v0.insertable_program[]) line 12 at SQL statement

---

select jobs_v0.insert_job('QmTXzATwNfgGVukV1fX2T6xw9f6LAYRVWpsdXyRWzUR2H9', array[row('main', E'\\xdeadbeef')]::jobs_v0.insertable_program[]);
ERROR:  malformed array literal: "13"
DETAIL:  Array value must start with "{" or dimension information.

score 2 · Accepted Answer · answered Mar 13 '20 at 05:48

2

The array literal should look like this:

select jobs_v0.insert_job(
          'QmTXzATwNfgGVukV1fX2T6xw9f6LAYRVWpsdXyRWzUR2H9',
          '{"(main,\"\\\\xdeadbeef\")"}'
       );

But the bug is in the function code:

INSERT INTO jobs_v0.programs(job, handle, package)
SELECT job_id, * FROM unnest(programs)
RETURNING * INTO inserted_programs;

The * in RETRUNING * does refer to all columns of jobs_v0.programs, not to all columns of jobs_v0.insertable_program, as you seem to expect.

Besides, inserted_programs cannot be of an array type, it would have to be of type jobs_v0.insertable_program and can contain only a single result. If the INSERT inserts several rows, only the first one will be returned.

answered Mar 13 '20 at 05:48

Laurenz Albe

209,280
17
206
263

Thanks for the response. If I remove the `RETURNING` statement all is well. However, my goal was the take all of the `insertable_program`s in the `programs` variable, insert them all with the job id generated in the first statement, and then accumulate the inserted rows into the `inserted_programs` variable. How would that be done? – Jorge Olivero Mar 13 '20 at 15:35
1

Then `LOOP` through the results of the `INSERT ... RETURNING` and add the rows to the array one by one. What are you going to do with the array? – Laurenz Albe Mar 13 '20 at 16:28

Constructing array literal as input for PL/pgSQL function

1 Answers1