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"

Answer 1

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.