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.
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.
User contributions licensed under CC BY-SA 3.0