PostgreSQL - Function with conditional local variable-CodePudding

I want to create a PostgreSQL function that will filter out table based on selected parameters.

However, I also need to perform some more complex logic based on the argument supplied. So I wanted to declare a local variable which would be based on some conditional logic.

What I have:

CREATE OR REPLACE FUNCTION get_something(parameter1 INT, parameter2 VARCHAR[])
DECLARE
    conditional_variable := (
        IF $1 = 50 THEN 
            'result-1'
        ELSIF $1 = 100 THEN 
            ARRAY['result-2', 'result-3']
        END IF;
    )
RETURNS TABLE (
    "time" BIGINT, 
    some_column NUMERIC
) AS $$
    SELECT 
        time,
        some_column
    FROM "SomeNiceTable" 
    WHERE time = $1
        AND some_dimension = ANY($2::VARCHAR[])
        AND some_other_dimension = ANY(conditional_variable::VARCHAR[]);
$$ LANGUAGE SQL;

But it does not work this way. Is there a way how to achieve such thing?

CodePudding user response：

You can not have DECLARE block and variables in a language sql function.

So you need to switch to language plpgsql and adjust the structure to be valid PL/pgSQL

CREATE OR REPLACE FUNCTION get_something(parameter1 INT, parameter2 VARCHAR[])
  RETURNS TABLE ("time" BIGINT, some_column NUMERIC) 
AS
$$
declare
  conditional_variable text[]; 
begin
  IF parameter1 = 50 THEN 
    conditional_variable := array['result-1'];
  ELSIF parameter1 = 100 THEN 
    conditional_variable := ARRAY['result-2', 'result-3']
  ELSE 
    ???? 
  END IF;

  return query    
    SELECT 
        time,
        some_column
    FROM "SomeNiceTable" 
    WHERE time = $1
        AND some_dimension = ANY($2::VARCHAR[])
        AND some_other_dimension = ANY(conditional_variable);
END;        
$$ 
LANGUAGE plpgsql;