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 DEFAULT 0,
	constant_value bpchar(255) NULL,
	locality_name bpchar(30) NOT NULL DEFAULT 1,
	CONSTRAINT s0constants_user_pk PRIMARY KEY (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(Edit SECOND) pass at this but now I have issues with "subquery must return only one column"

 

 

        FUNCTION 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
         ,case 
           when const_scopefk = 1 then --local
           ( select distinct scu.constant_value, c.constant_data_type
            from s0constants_user scu 
            
            where (scu.constant_name = constant_name 
               and scu.empfkey = empid
               and scu.locality_name = locality)
           limit 1)
               
           when const_scopefk  = 2 then --user
            (select scu.constant_value, c.constant_data_type
            from s0constants_user scu 
            
            where (scu.constant_name = constant_name 
               and scu.empfkey = empid
               and scu.locality_name = 1)
               limit 1)
         
          when const_scopefk  = 1 then --global
            (select scu.constant_value, c.constant_data_type
            from s0constants_user scu 
            
            where (scu.constant_name = constant_name
             and scu.empfkey = 0
            and scu.locality_name = 1)
            limit 1 )
        end 
       from public.s0constants c
       where  c.constant_name = UPPER(constname);
      
    	
    $$

   

EDIT: I've updated table definitions