I am new to advanced sql programming. I am storing constants.  
Constants have three possible scopes: local, user and global. 

Two tables: 

    s0constants_user holds set values of constants
    
    s0constants holds the constant definitions and values.

   
        CREATE TABLE public.s0constants_user (
    	constant_name bpchar(15) NOT NULL,
    	empfkey int2 NOT NULL,
    	constant_value bpchar(255) NULL,
    	locality_name bpchar(30) NULL,
    	id serial NOT NULL,
    	CONSTRAINT s0constants_user_pk PRIMARY KEY (id)
        );
       CREATE INDEX s0constants_user_constant_name_idx ON public.s0constants_user 
       USING btree (constant_name, empfkey, locality_name);

       CREATE TABLE public.s0constants (
	   constant_name bpchar(15) NOT NULL,
	   constant_value bpchar(255) NULL,
	   constant_data_type bpchar(1) NULL,
	   cttype int4 NULL,
	   displayname bpchar(30) NULL,
	   actiontype int4 NULL,
	   note text NULL,
	   const_scopefk int4 NOT NULL DEFAULT 3, -- 1=local 2- user 3=global
	   CONSTRAINT "S0CONSTANTS_pkey" PRIMARY KEY (constant_name),
	   CONSTRAINT scope_fk FOREIGN KEY (const_scopefk) REFERENCES 
       s0constants_scopelk(id)

I realize I could do this with separate tables for each scope but I'd like to make this work even if it is just a learning exercise.

I need a function to return and constant's value and data type given the constant name and empfkey.  There are different queries for each scope of constant.

    local - constant_name, 
    empfkey, 
    locality_name user - constant_name,
    empfkey global - constant_name

Constant names are unique over the three scopes.  I thought a case statement might do it but I'm having trouble limiting it to one row of data and how to set up the sub-queries.
This is my first pass at this but I can't figure out how to get the "cdt" column into the sub-queries or if this is event the right track:

 

    public.const_get( stname text, empid INT, locality text ) returns TABLE(cvalue text, ctype text)
    	LANGUAGE sql
    	 STABLE PARALLEL SAFE
    AS $$
    	
       select c.const_scopefk, c.constant_data_type as cdt
         ,case 
           when const_scopefk = 1 then
           ( select scu.constant_value, c.cdt
            from s0constants_user scu 
            where (scu.constant_name = constant_name 
               and scu.empfkey = empid
               and scu.locality_name = locality))
               
           when const_scopefk  = 2 then 
            (select scu.constant_value, c.cdt
            from s0constants_user scu 
            where (scu.constant_name = constant_name 
               and scu.empfkey = empid))
         
          when const_scopefk  = 1 then 
            (select scu.constant_value, c.cdt
            from s0constants_user scu 
            where (scu.constant_name = constant_name ))
        end 
       from public.s0constants c
       where  c.constant_name = UPPER(constname);