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);