PostgreSQL PL/pgSQL – Syntax Error at or Near ‘IF’

plpgsqlpostgresqlstored-procedures

I try to create a sp that checks if a job that didn't started already exists and if not creates a new one. It should return the id of the job.
For now I have the following code:

CREATE OR REPLACE FUNCTION 
public.create_category_training_job(category_id integer) RETURNS integer AS
$BODY$
-- check if a job for this category is already queued and not started
DECLARE job_id Integer := (SELECT id FROM category_training_jobs WHERE startdate IS NULL);
-- if a not started job is present return the id it
IF job_id IS NOT NULL THEN
    RETURN job_id;
-- if id is not present create a new job and return its id
ELSE    
    (INSERT INTO 
        category_training_jobs (category_id, submissiondate, startdate, enddate)
     VALUES
        (category_id, NOW(), NULL, NULL)
     RETURNING id);
END IF;
$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;

For some rease I get "ERROR: syntax error at or near "IF"
LINE 6: IF job_id IS NOT NULL THEN"

As far as I know IS NOT NULL should be a boolean expression, so it should be a valid expression. Am I missing something?

Best Answer

You are missing a BEGIN and END around your body:

REPLACE FUNCTION create_category_training_job(category_id integer) 
  RETURNS integer 
AS
$BODY$
-- check if a job for this category is already queued and not started
DECLARE 
  job_id Integer := (SELECT id FROM category_training_jobs WHERE startdate IS NULL);
BEGIN --<<<< HERE

  -- if a not started job is present return the id it
  IF job_id IS NOT NULL THEN
      RETURN job_id;
  -- if id is not present create a new job and return its id
  ELSE    
      INSERT INTO 
          category_training_jobs (category_id, submissiondate, startdate, enddate)
       VALUES
          (category_id, NOW(), NULL, NULL)
       RETURNING id;
  END IF;

END; --<<<< AND HERE
$BODY$
LANGUAGE plpgsql;

The parentheses around your insert are useless noise as well, you should remove them.

Related Solutions

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

PostgreSQL – Fixing Syntax Error at or Near INSERT

This works fine:

postgres=# create table hosts ( type varchar(20), name varchar(20));
CREATE TABLE
postgres=# \q
postgres@ironforge:~$ psql -c "insert into hosts (type,name) values ('test','10.100.133.1')"
INSERT 0 1
postgres@ironforge:~$

Best Answer

Related Solutions

Sql-server – deteriorating stored procedure running times

PostgreSQL – Fixing Syntax Error at or Near INSERT

Related Question