jeudi 29 mars 2018

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

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.

Aucun commentaire:

Enregistrer un commentaire