New to advanced sql stuff. I am storing constants. Constants have three possible scopes: local, users, 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);