PL/pgSQL syntax error: 42601

Question

I'm quite new to pl/pgsql and I have one issue: I'm trying to create function that will calculate tax based on pensja in:

create table pracownicy (
  id_pracownika int unique
, imie varchar(20)
, nazwisko varchar(20)
, miasto varchar(20)
, pensja real);

insert into pracownicy values
  (1, 'John', 'Smith', 'New York', 150)
, (2, 'Ben', 'Johnson', 'New York', 250)
, (3, 'Louis', 'Armstrong', 'New Orleans', 75)
, (4, 'John', 'Lennon', 'London', 300)
, (5, 'Peter', 'Gabriel', 'London', 100);

I've created function:

CREATE OR REPLACE FUNCTION pit(pensja real)
  RETURNS real AS
$BODY$
DECLARE
    dochod REAL;
    podatek REAL;
BEGIN
    dochod = 12*pensja;
      IF dochod <= 85528 THEN
         podatek = dochod*0,18-556,02;
         RETURN podatek;
ELSE
      podatek = 14839 + 0,32*(dochod - 85528);
      RETURN podatek;
END IF;
END;
$BODY$
  LANGUAGE plpgsql;

I'm trying to run it with:

select nazwisko, pensja, pit(pensja) from pracownicy;

Receiving:

ERROR: query "SELECT * dochod 0,18-556,02" returned 3 columns
SQL state: 42601
Context: function PL / pgSQL pit (real), line 8 in the assignment

And I'm not quite sure where the error is?

Jens · Accepted Answer · 2015-04-12 17:28:21Z

2

You have to use a dot as decimal separator:

  podatek = dochod*0.18-556.02;

answered Apr 12, 2015 at 17:28

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frank Heikens Over a year ago

And a link to the description of a DECIMAL: postgresql.org/docs/current/interactive/…

user3653415 Over a year ago

heh, I didn't see that coming ;) Thanks

Reflective Over a year ago

just want to add that using comma sepeartor isntead of dot separator, totally changes the view of the sql enginne of the expression, which 'thinks' you are returning 3 columns instead of one, because the column separator is ,. The error you recieve shows it: ERROR: query "SELECT * dochod 0,18-556,02" returned 3 columns - dochod 0 - first column, 18-556 - second column, 02 - third column

Erwin Brandstetter · Accepted Answer · 2015-04-15 18:38:38Z

0

Besides the primary issue with the decimal separator that @Jens pointed out, you can also largely simplify your function. Assignments are comparatively more expensive in plpgsql than in other popular languages.

CREATE OR REPLACE FUNCTION pit(pensja real)
  RETURNS real AS
$func$
DECLARE
   dochod real := 12 * pensja;
BEGIN
   IF dochod <= 85528 THEN
      RETURN dochod * 0.18 - 556.02;
   ELSE
      RETURN (dochod - 85528) * 0.32 + 14839;
   END IF;
END
$func$  LANGUAGE plpgsql;

answered Apr 15, 2015 at 18:38

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

PL/pgSQL syntax error: 42601

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related