Check function with IF statement in POSTGRESQL

Question

I have 10 functions, all 10 return '1' if the function has no errors and '0' if function has errors. I want to create another function witch calls all this functions and which checks it out if the functions return 0 or 1. After that, I want to run this function in linux crontab and the function's output (some text from if conditions) to go in a log file. I'm not sure if I can check this functions like this. Thanks for your time!

CREATE OR REPLACE FUNCTION public.test_al1()
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100 

AS $BODY$
DECLARE

BEGIN
    select public.test();
    if (select public.test()) = 1 then
        RAISE NOTICE 'No errors'
    else
        RAISE NOTICE 'Errors'
    end if;

END 
$BODY$;

select public.test(); this line will fail because you can't do a select in plpgsql without a destination for the result. — 404
– 404, Commented Jan 10, 2020 at 16:02
And you don't even need a SELECT, especially when the function returns a scalar. Just public.test() is enough. — sticky bit
– sticky bit, Commented Jan 10, 2020 at 16:07
Ok, so is enough public.test(). IF statement is good? Can I make (select public.test()) = 1? — scH
– scH, Commented Jan 10, 2020 at 16:19

Jim Jones · Accepted Answer · 2020-01-10 17:03:28Z

2

You were missing a return point for your query and there were also a few ; missing. I'm not really sure what you want to achieve with this function, since you declared the function would return TEXT and there is no RETURN statement.

One option would be to not return anything and use RAISE as you've been doing - keep in mind that the intention of RAISE (without INFO, EXCEPTION, etc.) alone is rather to report error messages:

CREATE OR REPLACE FUNCTION public.test_al1() RETURNS VOID LANGUAGE plpgsql 
AS $BODY$
BEGIN 
  IF public.test() = 1 THEN
    RAISE 'Errors';
  ELSE
    RAISE 'No errors';
  END IF;
END 
$BODY$;

.. or alternatively you can simplify it a bit by returning the message as TEXT in the RETURN clause.

CREATE OR REPLACE FUNCTION public.test_al1() RETURNS TEXT LANGUAGE plpgsql 
AS $BODY$
DECLARE res TEXT DEFAULT 'No errors';
BEGIN 
  IF public.test() = 1 THEN
    res := 'Errors';
  END IF;
  RETURN res;
END 
$BODY$;

7 Comments

user330315 Over a year ago

You don't even need the SELECT. if public.test() = 1 then ... should work just fine

Jim Jones Over a year ago

@a_horse_with_no_name you're right, silly me :-D thanks for pointing out

scH Over a year ago

Thx! If I want to run public.test() before if statement is enough to put ....BEGIN public.test() IF public.test() = 1 THEN...?

Jim Jones Over a year ago

@Lucian you don't need to run the function again before the if statement. Unless you store the result of this function a variable and compare this variable in your if statement. So, just execute your function once ;-)

scH Over a year ago

Yea, bun I want to run this function (witch calls all 10 function and check then) every day at a specific hour using a bash script in linux crontab, i don't want to run this functions manually every day.

|

Collectives™ on Stack Overflow

Check function with IF statement in POSTGRESQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related