Databases
StackOverflow2013
Postsdbo
PODynamic default values for table columns in Postgresql 9.1
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODynamic default values for table columns in Postgresql 9.1
    text
    copied!<p>I have a table called members</p> <pre><code>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 )) </code></pre> <p>each member type has a different sequence for the member code. i.e for gold members their member codes will be </p> <blockquote> <p>GLD0091, GLD0092,...</p> </blockquote> <p>and platinum members codes will be </p> <blockquote> <p>PLT00020, PLT00021,...</p> </blockquote> <p>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 </p> <p>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.</p> <pre><code>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; </code></pre> <p>where could i be going wrong? i get the following error</p> <pre><code>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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload