sql - Function to update a status flag for validity of other column? -

how create function compares card_id allowed_cards array elements?
if it's in array, column status must updated true.

create table client_1 (   id bigint not null,   "time" timestamp without time zone default now(),   status boolean,   card_id character varying(10),   constraint client_1_pkey primary key (id) );  create table allowed_cards (   allowed_cards character varying(10)[] );  insert allowed_cards values ('{dd3234,ff2342}'); 

enforce valid card_id @ times

first of all, if need table card_allowed, use plain varchar or text column (with multiple entries), not array:

create table card_allowed (   card_id varchar(10) not null primary key );  insert card_allowed values ('dd3234'), ('ff2342'); 

next, enforce valid cards, use fk constraint:

create table client_1 (   client_1_id  bigint not null primary key,   tstz         timestamptz default now(),   -- card_valid   boolean,   card_id      varchar(10),   constraint client_1_card_id_fk foreign key (card_id) references card_allowed ); 

since column can null, can leave empty if don't have valid card_id.

you don't need additional column (you named status, renamed card_valid).

i changed column names make more useful. among other things, using column name tstz instead of time, reserved word in standard sql , misleading actual timestamptz column.

if need allow invalid values in card_id (really?), fk constraint not possible. there other options:

only check new entries

you can "fake" immutable function runs check:

create or replace function f_card_allowed(text)   returns bool $func$ select exists (select 1 card_allowed card_allowed = $1); $func$   language sql stable;  -- not immutable 

the function not really immutable, because depends on values table. stable. result of same call can change between transactions. definition check constraints expect immutable functions, allow leeway (especially temporal functions) stable tolerated. should mark check constraint not valid document this, though:

alter table client_1 add constraint client_1_card_allowed check (f_card_allowed(card_id)) not valid; 

the difference: referential integrity not enforced @ times fk constraint. rows checked when inserted / updated , must valid @ time. no promises made status of existing rows: might have changed values in card_allowed now. details:

• constraint check values remotely related table (via join etc.)

set status once

now need additional flag card_valid commented out in table definition above. have no fk constraint:

update client_1 c set    card_valid = exists (select 1 card_allowed card_id = c.card_id); 

set status after every change

you same in trigger function every inserted / updated row:

create or replace function trg_client_1_insupbef()   returns trigger $body$ begin    new.card_valid := exists (select 1 card_allowed card_id = new.card_id);    return new; end $body$   language plpgsql;  create trigger insupbef before insert or update on client_1 each row execute procedure trg_client_1_insupbef();