Using 'Case' or 'If' statement in declaration portion of PL/PGSQL stored procedure

Question

As a newbie to PL/PGSQL I am trying to do something which works in (at least one) other PL/SQL environments and I haven't been able to isolate the way to do this in PL/PGSQL.

I am creating a function, and passing a variable into the function. With this variable I am building (or leaving blank) a variable which is then included into the final SQL Statement which is then executed.

I have left my attempts to use CASE and IF in the code adding comments to denote the attempts. I have looked up in the documentation which states there are SQL versions of CASE and IF and PL/PGSQL versions of CASE and IF, but I have not been able to nail down their differences and proper use of each, and, of course, whether the way I am trying to use it is allowed, or how to achieve this properly.

--DROP FUNCTION public._1_ExampleQuery(varchar);

CREATE OR REPLACE FUNCTION public._1_ExampleQuery(namefield varchar(50) DEFAULT 'name')
RETURNS TABLE(id double precision , name character varying(100), frc smallint) 
  LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
-- ------ GOAL ------------------------------------------------------------- 
--
--  If 'namefield' is blank, set namedwhere as empty, if namefield is 
--  present build sql structure to omit blanks

-- ------ Attempt using CASE ----------------------------------------------- 
--
--  CASE namefield
--      WHEN '' THEN
--          namedwhere varchar(50) := '';
--      ELSE 
--          namedwhere varchar(50) := ' and ' || namefield || ' <> '''' ';
--  END CASE;

-- ------ Attempt using If  ------------------------------------------------ 
--
--  IF namefield = '' THEN
--      namedwhere varchar(50) := '';
--  ELSE 
        namedwhere varchar(50) := ' and ' || namefield || ' <> '''' ';
--  END IF;
-- ---------------------------------------------------------------------------

sqlQuery varchar(500) := 'SELECT id,name,frc FROM road_nw WHERE frc = 1 ' || namedwhere;

BEGIN
    -- RAISE NOTICE 'SQL Query Statement: %',sqlQuery;
    RETURN QUERY EXECUTE sqlQuery;
END;

When I uncomment the CASE section I get this error:

ERROR:  syntax error at or near "CASE"
LINE 10:  CASE namefield
          ^
SQL state: 42601
Character: 480

And when I uncomment the IF Section I get this similar error:

ERROR:  syntax error at or near "IF"
LINE 17:  IF namefield = '' THEN
          ^
SQL state: 42601
Character: 752

The version of PostgreSQL that I am using is: PostgreSQL 9.3.19 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

So in summary, Can CASE and IF be used in the declaration portion of the function, and if so how? If they cannot, what is the proper way to achieve this goal of having a conditional portion of the SQL statement.

Answer 1

first problem, you can't use case-endcase or if-then-else-endif in the DECLARE section.

Second problem is SQL injection.

Also note varchar is the same as text, only (slightly) slower.

I added RETURNS NULL ON NULL INPUT because null would otherwise cause an error.

and added quote_identifier to block SQL injection

CREATE OR REPLACE FUNCTION public._1_ExampleQuery(namefield varchar(50) DEFAULT 'name')
RETURNS TABLE(id double precision , name character varying(100), frc smallint) 
  LANGUAGE 'plpgsql'
  RETURNS NULL ON NULL INPUT
AS $BODY$
DECLARE
-- ------ GOAL ------------------------------------------------------------- 
--
--  If 'namefield' is blank, set namedwhere as empty, if namefield is 
--  present build sql structure to omit blanks

  sqlQuery text;
  namedwhere text;
BEGIN

  IF namefield = '' THEN
     namedwhere := '';
  ELSE 
     namedwhere := ' and ' || quote_identifier(namefield) || ' <> '''' ';
  END IF;

  sqlQuery  := 'SELECT id,name,frc FROM road_nw WHERE frc = 1 ' || namedwhere;

    -- RAISE NOTICE 'SQL Query Statement: %',sqlQuery;
    RETURN QUERY EXECUTE sqlQuery;
END;
$BODY$;

if you want null namefield treated the same as empty, invert the if, and remove RETURNS NULL ON NULL INPUT

An alternative approach would be to use a CASE...END expression instead of the variables. (here I treat null the same a empty)

RETURN QUERY EXECUTE (  'SELECT id,name,frc FROM road_nw WHERE frc = 1 '
  || CASE WHEN namefield <> '' 
      THEN ' and ' || quote_identifier(namefield) || ' <> '''' ' 
      ELSE ''
     END );