0
votes

I am trying to create a function which calls 2 other functions.

Below is the calling function's code from where I am trying to call 2 another functions, schema1.func1() and schema1.func2().

But it is throwing error at the line SELECT schema1.func1(temp_val); saying:

syntax error at or near "SELECT".

I tried to figure out the correct syntax but couldn't resolve.

I am using Postgres version 1.14.3

DECLARE 
  temp_val   int;
  cursor1 CURSOR
  FOR 
  SELECT col1 from schema1.table1;

BEGIN
  OPEN cursor1;
  LOOP
    FETCH cursor1 INTO temp_val;
    EXIT WHEN NOT FOUND;
      SELECT CASE 
              WHEN NOT EXISTS (SELECT col2 FROM schema1.table2 WHERE col2 = temp_val) 
              THEN 
                BEGIN 
                SELECT schema1.func1(temp_val);
                SELECT schema1.func2(temp_val);
                END;
             END CASE;
  END LOOP;
  CLOSE cursor1;
END;
2
Can you post all your function definition, first lines miss ? - Rodolphe
And please be more precise on the PostgreSQL server version 1.14.3 is not a valid version number, is it 9.6 or 10 or other one ? - Rodolphe
did you put begin in select case when then construct?.. begin ..end; is plpgsql - you cant use them in query - Vao Tsun
I think it might work if he would remove that first SELECT (before CASE) and change those 2 executing functions into PERFORM. - Łukasz Kamiński
@ŁukaszKamiński Thanks a lot! It worked.. I used PEFORM instead of SELECT for calling the functions and removed the SELECT before CASE. - user9368451

2 Answers

0
votes

There is a ; missing at:

SELECT CASE WHEN NOT EXISTS (SELECT col2 FROM schema1.table2 WHERE col2 = temp_val)

so..

SELECT CASE WHEN NOT EXISTS (SELECT col2 FROM schema1.table2 WHERE col2 = temp_val);
0
votes

You can't mix PL/pgSQL BEGIN ... END blocks with SQL statement - even if that SQL statement is part of a PL/pgSQL function. So the BEGIN inside the THEN part of the CASE expression is invalid. A SQL CASE expression ends with just an END. A PL/pgSQL CASE statement would end with END CASE. As you are trying to use a CASE expression, it would require just END, not end case.

You also need to use perform in order to call a function where you want to discard the result.

It's not clear to me what exactly you want to achieve. If you only want to call those two functions if a row does not exist in table2 then you can do this with a single loop:

DECLARE 
  t1_rec record;
BEGIN

  FOR t1_rec IN select t1.col1
                from table1 t1
                where not exists (select *
                                  from table2 t2
                                  where t2.col2 = t1.col1)
  LOOP
    perform schema1.func1(t1_rec.col1);
    perform schema1.func2(t1_rec.col1);
  END LOOP;
END;