Syntax error in declaration of PL/pgSQL function

Question

Can anyone help me with this procedure? It's a pretty simple one, just want to insert some data into a table, but pgAdmin is giving me some errors.

This is the procedure code:

CREATE OR REPLACE FUNCTION FILL_INVOICE2(IN_NUM integer)
RETURNS void AS
DECLARE
    counter numeric := 0;
    BEGIN
    IF in_num > 1 THEN

    WHILE counter < 10
    LOOP
        INSERT INTO INVOICE(ID,INVOICE_ID,SUBSCRIBER_ID,AMOUNT,INVOICE_DATE,RECORD_DATE,INVOICE_TYPE,REST_TO_PAY,DESCRIPTION,INVOICE_REFERENCE)
VALUES(counter,counter,counter,100,current_date,current_date,1,100,'Telco services',1111);

         counter := counter + 1;
         RAISE NOTICE 'The counter is %', counter;
    END LOOP;
    END IF;

    RETURN;

  END;

Error is:

ERROR:  syntax error at or near "DECLARE counter numeric"
LINE 3: DECLARE
        ^
********** Error **********

ERROR: syntax error at or near "DECLARE counter numeric"
SQL state: 42601
Character: 75"

@almasshaikh, incorrect DECLARE comes before BEGIN. http://www.postgresql.org/docs/9.3/interactive/plpgsql-structure.html The problem is that what comes after AS should be a string. — Eelke, Dec 29 '14 at 10:40

score 1 · Answer 1 · answered Dec 29 '14 at 10:35

The body should be passed as a string

CREATE OR REPLACE FUNCTION FILL_INVOICE2(IN_NUM integer) RETURNS void AS 
$$
DECLARE
    counter numeric := 0; 
BEGIN
   IF in_num > 1 THEN
     WHILE counter < 10 LOOP 
       INSERT INTOI NVOICE(ID,INVOICE_ID,SUBSCRIBER_ID,AMOUNT,INVOICE_DATE,
            RECORD_DATE,INVOICE_TYPE,REST_TO_PAY,DESCRIPTION,INVOICE_REFERENCE)
       VALUES(counter,counter,counter,100,current_date,current_date,1,100,
          'Telco services',1111);
       counter := counter + 1;
       RAISE NOTICE 'The counter is %', counter;

     END LOOP; 
   END IF;
   RETURN;
END;
$$

You can use $$ to mark the beginning en end of a multiline string.

score 1 · Accepted Answer · edited May 23 '17 at 12:17

This would work:

CREATE OR REPLACE FUNCTION fill_invoice2(in_num integer)
  RETURNS void AS
$func$
DECLARE
   counter numeric := 0;
BEGIN
   IF in_num > 1 THEN
      WHILE counter < 10
      LOOP
         INSERT INTO invoice(ID,INVOICE_ID,SUBSCRIBER_ID,AMOUNT,INVOICE_DATE,RECORD_DATE
                            ,INVOICE_TYPE,REST_TO_PAY,DESCRIPTION,INVOICE_REFERENCE)
         VALUES(counter,counter,counter,100,current_date,current_date
               ,1,100,'Telco services',1111);

         counter := counter + 1;
         RAISE NOTICE 'The counter is %', counter;
      END LOOP;
   END IF;
END
$func$ LANGUAGE plpgsql;

Major points

Missing language declaration.
Missing quotes around function body. Preferrably, use dollar-quoting - like @Eelke advised. Details:
- Insert text with single quotes in PostgreSQL

But the whole function looks needlessly expensive.
Use a single INSERT based on generate_series() to replace the expensive loop with inserts per row. Optionally, you can wrap it in a function. Example with simple SQL function:

CREATE OR REPLACE FUNCTION fill_invoice2(in_num integer)
  RETURNS void AS
$func$
   INSERT INTO invoice(ID,INVOICE_ID,SUBSCRIBER_ID,AMOUNT,INVOICE_DATE,RECORD_DATE
                      ,INVOICE_TYPE,REST_TO_PAY,DESCRIPTION,INVOICE_REFERENCE)
   SELECT g,g,g,100,current_date,current_date,1,100,'Telco services',1111
   FROM   generate_series(0,10) g
   WHERE  $1 > 1;
$func$  LANGUAGE sql;

Does the same as your original.

I would also consider column defaults for some of your columns. For instance:

ALTER TABLE invoice
   ALTER COLUMN invoice_date SET DEFAULT current_date
 , ALTER COLUMN record_date  SET DEFAULT current_date;

Details:

converting mysql scripts to postgresql script

Then just don't mention those column in the INSERT statement and defaults are filled in automatically.

Syntax error in declaration of PL/pgSQL function

2 Answers2

Major points