PostgreSQL - Function with conditional local variable

Question

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?

Oliver Weichhold · Accepted Answer · 2022-10-19 10:08:19Z

2

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;

edited Oct 19, 2022 at 10:08

Oliver Weichhold

10.3k5 gold badges48 silver badges89 bronze badges

answered Oct 19, 2022 at 10:07

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

romanzdk Over a year ago

Thank you! It looks is works, however when calling the function I am getting column reference "time" is ambiguous - why is that?

user330315 Over a year ago

Because you have a column time in the result and a column time in table. Using a fully qualified reference in the SELECT should fix this: select "SomeNiceTable"."time" ...

Collectives™ on Stack Overflow

PostgreSQL - Function with conditional local variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related