CODEWITHSUNDEEP
databasepostgresql
indago
indago

Reputation: 2101

Dynamic default values for table columns in Postgresql 9.1

I have a table called members

CREATE TABLE netcen.mst_member
(
  mem_code character varying(8) NOT NULL,
  mem_name text NOT NULL,
  mem_cnt_code character varying(2) NOT NULL,
  mem_brn_code smallint NOT NULL, -- The branch where the member belongs
  mem_email character varying(128),
  mem_cell character varying(11),
  mem_address text,
  mem_typ_code smallint NOT NULL,
  CONSTRAINT mem_code PRIMARY KEY (mem_code ))

each member type has a different sequence for the member code. i.e for gold members their member codes will be

GLD0091, GLD0092,...

and platinum members codes will be

PLT00020, PLT00021,...

i would like to have the default value for the field mem_code as a dynamic value depending on the member type selected. how can i use a check constraint to implement that?? please help, am using Postgresql 9.1

i have created the following trigger function to construct the string but i still get an error when i insert into the members table as Randy said.

CREATE OR REPLACE FUNCTION netcen.generate_member_code()
  RETURNS trigger AS
$BODY$DECLARE 
tmp_suffix text :='';
tmp_prefix text :='';
tmp_typecode smallint ;
cur_setting refcursor;
BEGIN
OPEN cur_setting FOR
EXECUTE 'SELECT typ_suffix,typ_prefix,typ_code FROM mst_member_type WHERE type_code =' || NEW.mem_typ_code ;
FETCH cur_setting into tmp_suffix,tmp_prefix,tmp_typecode;
CLOSE cur_setting;
NEW.mem_code:=tmp_prefix || to_char(nextval('seq_members_'|| tmp_typecode), 'FM0000000') || tmp_suffix;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION netcen.generate_member_code()
  OWNER TO mnoma;

where could i be going wrong? i get the following error

ERROR:  relation "mst_member_type" does not exist
LINE 1: SELECT typ_suffix,typ_prefix,typ_code FROM mst_member_type W...
                                                   ^
QUERY:  SELECT typ_suffix,typ_prefix,typ_code FROM mst_member_type WHERE typ_code =1
CONTEXT:  PL/pgSQL function "generate_member_code" line 7 at OPEN

Upvotes: 1

Views: 1383

Answers (1)

Randy
Randy

Reputation: 16677

i think this is a normalization problem.

the codes you provide are derivable from other information - therefore really do not belong as independent columns.

you could just store the type in one column, and the number in another - then on any query where needed append them together to make this combo-code.

if you want to persist this denormalized solution, then you could make a trigger to construct the string on any insert or update.

Upvotes: 3

Related Questions