Constructing array literal as input for PL/pgSQL function

1

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.


postgresql
plpgsql
composite-types
asked on Stack Overflow Mar 13, 2020 by Jorge Olivero

1 Answer

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 on Stack Overflow Mar 13, 2020 by Laurenz Albe

User contributions licensed under CC BY-SA 3.0