PL/pgSQL列名与变量相同 [英] PL/pgSQL column name the same as variable

问题描述

我是plpgsql的新手，我正在尝试创建一个函数，该函数将检查表中是否存在某个值，否则将添加一行.

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;

问题是我说id_pracownika是列名和变量时遇到错误.

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

在这种情况下如何指定"id_pracownika"是指列名?

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

推荐答案

CREATE OR REPLACE FUNCTION hire(
    id_pracownika integer
  , imie varchar
  , nazwisko varchar
  , miasto varchar
  , pensja real)
  RETURNS text LANGUAGE plpgsql AS
$func$
BEGIN
  IF EXISTS (SELECT 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$;

• 使用名称来对列名和前缀函数参数进行模式限定，以便在必要时消除歧义.
  但是请注意，INSERT的目标列表中的列名称可能没有前缀.无论如何这些都不是模棱两可的.

  • 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.

    最好一开始就避免此类歧义，因为这样不容易出错.如果还需要列名作为函数参数名，则避免命名冲突的一种方法是使用常规位置:在这种情况下，id_pracownika为 $1 .
    最后，您可以通过设置#variable_conflict决定优先级.参见:

    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 also, one way to avoid naming collisions is to an ALIAS inside the function. One of the rare cases where ALIAS is actually useful.
    Or you can reference input parameters by ordinal position: $1 for id_pracownika in this case.
    Finally, you can decide what takes precedence by setting #variable_conflict. See:

    • 函数参数和使用USING子句的JOIN结果之间的命名冲突

    但是还有更多:

    • 字符串文字(文本常量)必须用单引号引起来:'OK'，而不是 "OK" .

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

    在EXISTS表达式中，选择什么都没有关系. SELECT id_pracownika与SELECT 1或什至SELECT 123/0相同.或一个空的SELECT列表，如所示.仅存在任何符合条件的行.

    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. Or an empty SELECT list like demonstrated. Only the existence of any qualifying row matters.

    • 在EXISTS子查询中更容易阅读的是什么?

    分配变量比其他编程语言要昂贵得多.尽量减少分配，以在plpgsql中获得最佳性能.直接在SQL语句中执行尽可能多的操作.

    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是函数的默认修饰符.不需要把那些拼出来.

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

    您的函数是"SELECT或INSERT"的另一种实现，它是UPSERT问题的一种变体，面对并发写入负载，它比您想象的要复杂. (简单的解决方案可能会忽略潜在的问题，并且可能使它失败！)详细信息:

    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:

    • 是SELECT还是在容易出现竞争状况的函数中插入

    Postgres团队(最著名的是Peter Geoghegan)已经在Postgres 9.5中实现了UPSERT. Postgres Wiki中的详细信息.此新语法将完成干净的工作 >:

    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;
    

    这篇关于PL/pgSQL列名与变量相同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！