I have a composite type:
CREATE TYPE objref AS (
objid nonnull_int8,
objtype nonnull_string
);
Have a table with an array column of the above composite type:
CREATE TABLE partner {
sw objref ARRAY
}
I would like to add a GENERATED column to the ‘partner’ table which is made of all the objid elements in the sw array. Tried the statement below (and many others) but ran into syntax errors
alter table partner add column sw_id int8[] GENERATED ALWAYS AS sw[:].objid STORED;
Looked into the postgres documentation but could not find a usable example.
Disclaimer: An array of composite type using custom domains as data type of a table column … This level of sophistication leads to problems. Very tricky syntax. Simplify! Like, create an additional simple table with scalar types instead.
Setup
What you show is not legal Postgres syntax. I assume you mean a setup like this:
CREATE DOMAIN nonnull_int8 AS int8 NOT NULL;
CREATE DOMAIN nonnull_text AS text NOT NULL;
CREATE TYPE objref AS (
objid nonnull_int8
, objtype nonnull_text
);
CREATE TABLE partner (
sw objref[]
);
INSERT INTO partner VALUES
('{"(1,foo)","(2,bar)"}')
, ('{"(3,baz)","(4,bam)"}')
RETURNING *;
| sw |
|---|
| {“(1,foo)”,”(2,bar)”} |
| {“(3,baz)”,”(4,bam)”} |
NOT NULL can’t be enforced in CREATE TYPE. You need a DOMAIN for this.
Your attempt
While this is a legal expression: sw[1].objid
This (though it would seem convenient) is not: sw[:].objid
Column notation can only be applied to a composite type.
sw[:] produces an array – effectively the same as the original sw, but stripping custom array indices (if any). See:
- Normalize array subscripts so they start with 1
You must unnest the array before you can access columns of the composite type.
Plus, the syntax for creating a generated column requires parentheses around the expression.
Solution
Proof of concept. (I would simplify instead.)
Create an auxiliary function wrapper for the necessary subquery (which would not be allowed in an expression for a generated column). Then you can add the generated column:
CREATE OR REPLACE FUNCTION f_objref_ids(objref[])
RETURNS int8[]
LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT o_r.objid FROM unnest($1) o_r);
ALTER TABLE partner
ADD COLUMN sw_id int8[] GENERATED ALWAYS AS (f_objref_ids(sw)) STORED;
fiddle
About generated columns:
- Computed / calculated / virtual / derived / generated columns in PostgreSQL
About the used standard SQL (short) syntax for CREATE FUNCTION:
- What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?
1