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)

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

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)

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

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:

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: