postgresql - PL/pgSQL column name the same as variable

Question

I'm new to plpgsql and I'm trying to create function that will check if a certain value exists in table and if not will add a row.

CREATE OR REPLACE FUNCTION hire(
    id_pracownika integer,
    imie character varying,
    nazwisko character varying,
    miasto character varying,
    pensja real)
  RETURNS TEXT AS
$BODY$
DECLARE
wynik TEXT;
sprawdzenie INT;
BEGIN
sprawdzenie = id_pracownika;
IF EXISTS (SELECT id_pracownika FROM pracownicy WHERE id_pracownika=sprawdzenie) THEN
wynik = "JUZ ISTNIEJE";
RETURN wynik;
ELSE
INSERT INTO pracownicy(id_pracownika,imie,nazwisko,miasto,pensja)
VALUES (id_pracownika,imie,nazwisko,miasto,pensja);
wynik = "OK";
RETURN wynik;   
END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

The issue is that I'm getting errors saying that id_pracownika is a column name and a variable.

How to specify that "id_pracownika" in such context refers to column name?

Answer 1

Assuming id_pracownika is The PRIMARY KEY of the table. Or at least defined UNIQUE. (If it's not NOT NULL, NULL is a corner case.)

SELECT or INSERT

Your function is another implementation of "SELECT or INSERT" - a variant of the UPSERT problem, which is more complex in the face of concurrent write load than it might seem. See:

• Is SELECT or INSERT in a function prone to race conditions?

With UPSERT in Postgres 9.5 or later

In Postgres 9.5 or later use UPSERT (INSERT ... ON CONFLICT ...) Details in the Postgres Wiki. This new syntax does a clean job:

CREATE OR REPLACE FUNCTION hire(
        _id_pracownika integer
      , _imie varchar
      , _nazwisko varchar
      , _miasto varchar
      , _pensja real)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO pracownicy
          ( id_pracownika, imie, nazwisko, miasto, pensja)
   VALUES (_id_pracownika,_imie,_nazwisko,_miasto,_pensja);
   ON     CONFLICT DO NOTHING
   RETURNING 'OK';

   IF NOT FOUND THEN
      RETURN 'JUZ ISTNIEJE';
   END IF;
END
$func$;

Table-qualify column names to disambiguate where necessary. (You can also prefix function parameters with the function name, but that gets awkward, easily.)
But column names in the target list of an INSERT may not be table-qualified. (Never ambiguous anyway.)

Best avoid such ambiguities a priori, that's less error prone. Some (including me) like to do that by prefixing all function parameters and variable with an underscore.

If you positively need a column name as function parameter name also, one way to avoid naming collisions is to use an ALIAS inside the function. One of the rare cases where ALIAS is actually useful.

Or reference function parameters by ordinal position: $1 for id_pracownika in this case.

If all else fails, you can decide what takes precedence by setting #variable_conflict. See:

• Naming conflict between function parameter and result of JOIN with USING clause

There is more:

• There are intricacies to the RETURNING clause in an UPSERT. See:

  • How to use RETURNING with ON CONFLICT in PostgreSQL?

• String literals (text constants) must be enclosed in single quotes: 'OK', not "OK". See:

  • Insert text with single quotes in PostgreSQL

• Assigning variables is comparatively more expensive than in other programming languages. Keep assignments to a minimum for best performance in plpgsql. Do as much as possible in SQL statements directly.

• VOLATILE COST 100 are default decorators for functions. No need to spell those out.

Without UPSERT in Postgres 9.4 or older

...
   IF EXISTS (SELECT FROM pracownicy p
             WHERE  p.id_pracownika = hire.id_pracownika) THEN
      RETURN 'JUZ ISTNIEJE';
   ELSE
      INSERT INTO pracownicy(id_pracownika,imie,nazwisko,miasto,pensja)
      VALUES (hire.id_pracownika,hire.imie,hire.nazwisko,hire.miasto,hire.pensja);
    
      RETURN 'OK';
   END IF;
...

In an EXISTS expression, the SELECT list does not matter. SELECT id_pracownika, SELECT 1, or even SELECT 1/0 - all the same. Just use an empty SELECT list. Only the existence of any qualifying row matters. See:

• What is easier to read in EXISTS subqueries?