Dynamic default values for table columns in Postgresql 9.1

Question

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

I had forgotten to include the schema name on the select query, should be: FROM netcen.mst_member_type Now there is no error but its not writing anything on the mem_code column. its just blank?? — indago
– indago, Commented Jan 14, 2013 at 15:12

Randy · Accepted Answer · 2013-01-14 13:58:38Z

3

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.

answered Jan 14, 2013 at 13:58

Randy

16.7k2 gold badges40 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

indago Over a year ago

i have added a trigger function to construct the string as you said but 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 type_code =1 CONTEXT: PL/pgSQL function "generate_member_code" line 7 at OPEN where am i going wrong?

Collectives™ on Stack Overflow

Dynamic default values for table columns in Postgresql 9.1

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related