这可能听起来像一个非常愚蠢的问题,但我如何声明一个用于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
Erwin Brands.. 21
您的演示功能将如下工作:
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;
您不能用于SET
分配变量.这被认为是用于设置运行时参数的SQL命令SET
.
但是你可以在声明时分配一个变量,甚至可以使用子查询.
使用LANGUAGE plpgsql
,而不是.这是一个标识符.LANGUAGE 'plpgsql'
@a_horse_with_no_name已经写过关于命名冲突的文章.
调试代码时,使用干净的格式有很长的路要走......
但你可以简化为:
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 $1, max(cod_instance), now() , 'information', 'start process_' || $1 FROM public.instance WHERE cod_process = $1 GROUP BY cod_process RETURNING 11 $func$ LANGUAGE sql;
呼叫:
SELECT public.test(); -- for default 30001 SELECT public.test(1234);
并根据utc_log
您可能想要的实际数据类型now() AT TIME ZONE 'UTC'
:
在Rails和PostgreSQL中完全忽略时区
这标志着Stack Overflow用户很少使用Postgres这样的答案投票如此之少. (2认同)
a_horse_with.. 20
您需要使用into
实际代码块内的子句运行select ,而不是在declare
块中:
begin select max(cod_instance) into cod_instance from public.instance where public.instance.cod_process=cod_process; .... end;
赋予变量(或参数)与表中的列相同的名称通常不是一个好主意.在某些情况下,这可能会使解析器混淆.为避免任何潜在问题,请尝试为变量使用不同的名称,例如通过为它们添加前缀(例如,l_cod_process
代替cod_process
或l_cod_instance
代替cod_instance
)
有关变量赋值的更多详细信息,请参见手册:http://www.postgresql.org/docs/current/static/plpgsql-statements.html