postgresql 声明变量集 = 选择

Declare variable set = select

提问by ASA

This probably sounds like a really stupid question, but how do I declare a variable for used in a PostgreSQL 9.3 query?

这听起来可能是一个非常愚蠢的问题，但是我如何声明一个用于 PostgreSQL 9.3 查询的变量？

CREATE or replace FUNCTION public.test()
  returns int4
AS
$BODY$
DECLARE    
    cod_process bigint :=30001;
    cod_instance bigint ;
    utc_log timestamp without time zone := localtimestamp;
    cod_log_type varchar(100) :='information ';
    txt_log_text varchar(100):= 'start process';
    txt_log varchar(100):= txt_log_text||'_'||cod_process;
    set cod_instance= select max(cod_instance) as cod_instance from public.instance where public.instance.cod_process=cod_process;    
BEGIN  
    INSERT INTO public.log (cod_process, cod_instance, utc_log,cod_log_type,txt_log)
    VALUES (cod_process, cod_instance, utc_log,cod_log_type,txt_log );
    RETURN 11;
END;
$BODY$ LANGUAGE 'plpgsql';

ERROR: type "cod_instance" does not exist
SQL state: 42704
Character: 383

ERROR: type "cod_instance" does not exist
SQL state: 42704
Character: 383

回答by a_horse_with_no_name

You need to run the select using the intoclause inside the actual code block, not in the declareblock:

您需要使用into实际代码块内的子句运行选择，而不是在declare块中：

begin
   select max(cod_instance) 
      into cod_instance
   from public.instance 
   where public.instance.cod_process=cod_process;

   ....
end;

It's usually not such a good idea to give variables (or parameters) the same name as columns in the table. There are certain cases where this can confuse the parser. To avoid any potential problems, try to use different names for your variables, e.g. by prefixing them (e.g. l_cod_processinstead of cod_processor l_cod_instanceinstead of cod_instance)

为变量（或参数）赋予与表中的列相同的名称通常不是一个好主意。在某些情况下，这可能会混淆解析器。为避免任何潜在问题，请尝试为变量使用不同的名称，例如通过为它们添加前缀（例如l_cod_process代替cod_process或l_cod_instance代替cod_instance）

More details on variable assignment can be found in the manual: http://www.postgresql.org/docs/current/static/plpgsql-statements.html

有关变量分配的更多详细信息，请参见手册：http: //www.postgresql.org/docs/current/static/plpgsql-statements.html

回答by Erwin Brandstetter

Your demo function would work like this:

您的演示功能将如下工作：

CREATE or replace FUNCTION public.test()
  RETURNS int4 AS
$func$
DECLARE
   _cod_process  bigint := 30001;
   _cod_instance bigint := (SELECT max(cod_instance)
                            FROM   public.instance
                            WHERE  cod_process = _cod_process);
   _utc_log      timestamp := localtimestamp;
   _cod_log_type varchar(100) := 'information';
   _txt_log_text varchar(100) := 'start process';
   _txt_log      varchar(100) := txt_log_text || '_' || cod_process;
BEGIN
   INSERT INTO public.log
          ( cod_process,  cod_instance,  utc_log,  cod_log_type,  txt_log)
   VALUES (_cod_process, _cod_instance, _utc_log, _cod_log_type, _txt_log);

   RETURN 11;
END
$func$ LANGUAGE plpgsql;

Major points

要点

• You cannotuse SETto assign a variable. That's taken to be the SQL command SETfor setting run-time parameters.

• But you canassign a variable at declaration time, even use a subquery for that.

• UseLANGUAGE plpgsql, not LANGUAGE 'plpgsql'. It's an identifier.

• @a_horse_with_no_namealready wrote about naming conflicts.

• Using a clean format goes a long way when debugging code ...

• 您不能用于SET分配变量。这被认为是用于设置运行时参数的SQL 命令SET。

• 但是您可以在声明时分配一个变量，甚至可以为此使用子查询。

• 使用LANGUAGE plpgsql，不是LANGUAGE 'plpgsql'。它是一个标识符。

• @a_horse_with_no_name已经写过关于命名冲突的文章。

• 在调试代码时，使用干净的格式大有帮助......

---

---

But you can probably simplify to:

但您可能可以简化为：

CREATE OR REPLACE FUNCTION public.test(_cod_process bigint = 30001)
  RETURNS integer AS
$func$
   INSERT INTO public.log
   (cod_process, cod_instance     , utc_log, cod_log_type , txt_log)
   SELECT    , max(cod_instance), now()  , 'information', 'start process_' || 
   FROM   public.instance
   WHERE  cod_process = 
   GROUP  BY cod_process
   RETURNING 11
$func$ LANGUAGE sql;

Call:

称呼：

SELECT public.test();     -- for default 30001
SELECT public.test(1234);

And depending on the actual data type of utc_logyou probably want now() AT TIME ZONE 'UTC':

并取决于utc_log您可能想要的实际数据类型now() AT TIME ZONE 'UTC'：

• Ignoring timezones altogether in Rails and PostgreSQL

• 在 Rails 和 PostgreSQL 中完全忽略时区