PL/pgSQL column name the same as variable

问题:

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?

回答1:

CREATE OR REPLACE FUNCTION hire(
    id_pracownika integer
  , imie varchar
  , nazwisko varchar
  , miasto varchar
  , pensja real)
  RETURNS TEXT AS
$func$
BEGIN

IF EXISTS (SELECT 1 FROM pracownicy p
           WHERE p.id_pracownika = hire.sprawdzenie) THEN
   RETURN 'JUZ ISTNIEJE'::text;  -- wynik
ELSE
   INSERT INTO pracownicy(id_pracownika,imie,nazwisko,miasto,pensja)
   VALUES (hire.sprawdzenie,hire.imie,hire.nazwisko,hire.miasto,hire.pensja);

   RETURN 'OK'::text;  -- wynik
END IF;

END
$func$  LANGUAGE plpgsql;

• Like @pozs commented, schema-qualify column names and prefix function parameters with the function name to disambiguate where necessary. Be aware, however, that column names in the target list of an INSERT may not be prefixed. Those are never ambiguous anyway.

• Like @Frank commented, it's best to avoid such ambiguities to begin with, that's less error prone. If you need a column name as function parameter name, too, one way to avoid naming collisions would be to use an ALIAS inside the function. That's one of the rare cases where ALIAS is actually useful. Or you can reference input parameter by ordinal position: $1 for id_pracownika in this case.

But there is more:

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

• The assignment operator in plpgsql is :=.

  • The forgotten assignment operator "=" and the commonplace ":="

• In an EXISTS expression, it does not matter what you select. SELECT id_pracownika is the same as SELECT 1 or even SELECT 123/0. Only the existence of a rows matters.

  • What is easier to read in EXISTS subqueries?

• Assigning variables is comparatively more expensive than in other programming languages. If there is an elegant way to save some of those operations, that's the preferable way in plpgsql. Do as much as possible in SQL statements directly.

• VOLATILE COST 100 are default decorators for functions. You don't have to spell those out.

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 you might imagine. (Potential problems your simple solution ignores, btw, and can make it fail!) Details:

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

UPSERT in Postgres 9.5

The Postgres team (most prominently Peter Geoghegan) has implemented UPSERT in Postgres 9.5. Details in the Postgres Wiki. This new syntax will do a clean job:

   INSERT INTO pracownicy(id_pracownika,imie,nazwisko,miasto,pensja)
   VALUES (hire.sprawdzenie,hire.imie,hire.nazwisko,hire.miasto,hire.pensja);
   ON CONFLICT DO NOTHING
   RETURNING 'OK'::text;   -- wynik

   IF NOT FOUND THEN
      RETURN 'JUZ ISTNIEJE'::text;  
   END IF;

回答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');