Postgresql – Need a function to run different queries based on parameters sent subquery must return one column error

functionspostgresql-11

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

Best Answer

So I finally got this to work with this function: 

returns table(  ctype int, cvalue char )
    LANGUAGE sql
     STABLE PARALLEL SAFE
AS $$

   select c.cttype, 
     case 
       when const_scopefk = 1 then --local
       ( select scu.constant_value
        from s0constants_user scu 

        where (scu.constant_name = c.constant_name 
           and scu.empfkey = empid
           and scu.locality_name = locality)
       limit 1)

       when const_scopefk  = 2 then --user
        (select scu.constant_value
        from s0constants_user scu 

        where (scu.constant_name = c.constant_name 
           and scu.empfkey = empid
           and scu.locality_name = '1')
           limit 1)

      when const_scopefk  = 1 then --global
        ( select scu.constant_value
        from s0constants_user scu 

        where (scu.constant_name = c.constant_name
         and scu.empfkey = 0
        and scu.locality_name = '1')
        limit 1 )
    end 
   from public.s0constants c
   where  c.constant_name = UPPER(stname);


$$;````