Re: Two academic questions

[Дмитрий Иванов <firstdismay@xxxxxxxxx>]

ср, 2 февр. 2022 г. в 09:19, David G. Johnston <david.g.johnston@xxxxxxxxx>:

On Tue, Feb 1, 2022 at 8:15 PM Дмитрий Иванов <firstdismay@xxxxxxxxx> wrote:

Why are COLUMN GENERATED ALWAYS AS called "methods" and singled out as a separate field type?

This sounds like you are describing something written.  Can you provide a link to where that is?

Is there a common name in the community for the approach in which the data schema is presented as data?

It is possible to give a link, but it would require authorization:


Here are the contents of the topic:

====================

Actual code include ALWAYS AS column:
CREATE TABLE bpd.schedules_calendar
(
    id bigint NOT NULL DEFAULT nextval('bpd.work_calendar_id_seq'::regclass),
    work_date date NOT NULL,
    work_year integer NOT NULL GENERATED ALWAYS AS (date_part('year'::text, work_date)) STORED,
    work_month integer NOT NULL GENERATED ALWAYS AS (date_part('month'::text, work_date)) STORED,
    work_day integer NOT NULL GENERATED ALWAYS AS (date_part('day'::text, work_date)) STORED,
    day_type bpd.day_type NOT NULL,
    name_holiday character varying(100) COLLATE pg_catalog."default" NOT NULL DEFAULT 'будний день'::character varying,
    week40_day interval NOT NULL,
    week40_month interval NOT NULL,
    week36_day interval NOT NULL,
    week36_month interval NOT NULL,
    week35_day interval NOT NULL,
    week35_month interval NOT NULL,
    week24_day interval NOT NULL,
    week24_month interval NOT NULL,
    CONSTRAINT work_calendar_pkey PRIMARY KEY (id)
)
The code suggested in the studio:
CREATE TABLE "bpd"."schedules_calendar" (
    "id" BigInt DEFAULT nextval('bpd.work_calendar_id_seq'::regclass) NOT NULL,
    "work_date" Date NOT NULL,
    "work_year" Integer DEFAULT date_part('year'::text, work_date) NOT NULL,
    "work_month" Integer DEFAULT date_part('month'::text, work_date) NOT NULL,
    "work_day" Integer DEFAULT date_part('day'::text, work_date) NOT NULL,
    "day_type" "bpd"."day_type" NOT NULL,
    "name_holiday" Character Varying( 100 ) DEFAULT 'будний день'::character varying NOT NULL,
    "week40_day" Interval NOT NULL,
    "week40_month" Interval NOT NULL,
    "week36_day" Interval NOT NULL,
    "week36_month" Interval NOT NULL,
    "week35_day" Interval NOT NULL,
    "week35_month" Interval NOT NULL,
    "week24_day" Interval NOT NULL,
    "week24_month" Interval NOT NULL,
    PRIMARY KEY ( "id" ) );

DEFAULT VALUE <> GENERATED ALWAYS AS STORED
the DEFAULT value is not recalculated when the reference column are changed. is not an identical construction GENERATED ALWAYS AS STORED

reply:

They are in a separate list “Methods”.
This approach was used for other databases long before generated columns were implemented in PostgreSQL.

====================

 

Which community?  There are many ways in which a "data schema [can be] presented as data".  In PostgreSQL there is only a single source of truth for what the data schema is - the "System Catalogs" [1].  Those tables are made available to the user in the pg_catalog schema.

David J.

I came across a long acronym defining the name of the approach, then I decided that to come up with something fundamentally new is difficult enough. That everything already has a formal name. But I can't find it anymore. I need it to position my solution.

Shema - shema

Table Entity

id | propery1| property2| property3

Shema-data

Table Entity

id| name

Table Property Entity

id | id_entity | name| val

--

Regards, Dmitry!