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;

   IF FOUND THEN
      RETURN 'OK';
   ELSE
      RETURN 'JUZ ISTNIEJE';  -- already exists
   END IF;
END
$func$;

About the special variable FOUND:

• Why is IS NOT NULL false when checking a row type?

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

Best avoid ambiguities a priori. Some (including me) like to prefix all function parameters and variables with an underscore.

If you positively need a column name as function parameter name, 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;
...

But there is a tiny race condition between the SELECT and the INSERT, so not bullet-proof under heavy concurrent write-load.

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?

Answer 2

It is a example tested by me where I use EXECUTE to run a select and put its result in a cursor, using dynamic column names.

1. Create the table:

create table people (
  nickname varchar(9),
  name varchar(12),
  second_name varchar(12),
  country varchar(30)
  );

2. Create the function:

CREATE OR REPLACE FUNCTION fun_find_people (col_name text, col_value varchar)
RETURNS void AS
$BODY$
DECLARE
    local_cursor_p refcursor;
    row_from_people RECORD;

BEGIN
    open local_cursor_p FOR
        EXECUTE 'select * from people where '|| col_name || ' LIKE ''' || col_value || '%'' ';

    raise notice 'col_name: %',col_name;
    raise notice 'col_value: %',col_value;

    LOOP
        FETCH local_cursor_p INTO row_from_people; EXIT WHEN NOT FOUND;

        raise notice 'row_from_people.nickname: %',  row_from_people.nickname ;
        raise notice 'row_from_people.name: %', row_from_people.name ;
        raise notice 'row_from_people.country: %', row_from_people.country;
    END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql'

3. Run the function select fun_find_people('name', 'Cristian'); select fun_find_people('country', 'Chile');

Answer 3

inspire with Erwin Brandstetter's answers.

CREATE OR REPLACE FUNCTION test_upsert(
        _parent_id int, 
        _some_text text)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
DECLARE a text;
BEGIN
   INSERT INTO parent_tree (parent_id, some_text)
   VALUES (_parent_id,_some_text)
   ON     CONFLICT DO NOTHING
   RETURNING 'ok' into a;
   return a;

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

1. Follow Erwin's answer. I make a variable hold the return type text.
2. If conflict do nothing then the function will return nothing. For example, already have parent_id = 10, Then the result would be as following:

test_upsert
------------

(1 row)

3. NOT Sure the usage of:

   IF NOT FOUND THEN
        return 'JUZ ISTNIEJE';
   END IF;