Re: Function to set up variable inside it
| От | Pavel Stehule |
|---|---|
| Тема | Re: Function to set up variable inside it |
| Дата | |
| Msg-id | CAFj8pRA5DHO0-PZpCiVR9J+Temcvf64Ye0H45gU7yKZghfzXPQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Function to set up variable inside it (Łukasz Jarych <jaryszek@gmail.com>) |
| Ответы |
Re: Function to set up variable inside it
(Łukasz Jarych <jaryszek@gmail.com>)
|
| Список | pgsql-general |
Hi
2018-05-16 6:52 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:
Hi Guys,thank you for your help !Hmm yes something like this.I was wondering if is possible to pass variable into function:CREATE FUNCTION change_trigger() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO logging.t_history (tabname, schemaname, operation, new_val) VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW)); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val) VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW), row_to_json(OLD)); RETURN NEW; ELSIF TG_OP = 'DELETE' THEN INSERT INTO logging.t_history (tabname, schemaname, operation, old_val) VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD)); RETURN OLD; END IF; END; $$ LANGUAGE 'plpgsql' SECURITY DEFINER;Best,Jacek
It is possible to pass values into functions -
postgres=# select fx(10);
NOTICE: >>>10<<<
┌────┐
│ fx │
╞════╡
│ │
└────┘
(1 row)
postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx(a integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
raise notice '>>>%<<<', a;
end;
$function$
NOTICE: >>>10<<<
┌────┐
│ fx │
╞════╡
│ │
└────┘
(1 row)
postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx(a integer)
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
raise notice '>>>%<<<', a;
end;
$function$
but it is not possible to pass parameters to trigger functions. You can define trigger parameters when you define trigger, but these values must be constant.
a) the request of trigger parametrization is usually signal of bad using of triggers - probably you should to use a function, not trigger
b) there is workaround - but you should not to use it if it is not really necessary
There are few implementations of session variables in postgres - you can find via google. Then you can set session variable before SQL command, and you can read this session variable inside trigger function.
Regards
Pavel
2018-05-15 14:58 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:On 05/15/2018 05:28 AM, Łukasz Jarych wrote:Hi Guys,
I am using postgres 10.3 (or 4?).
IT is possible to set up variable inside function?
Like this?:
https://www.postgresql.org/docs/10/static/plpgsql-declaratio ns.html
Best,
Jacek
--
Adrian Klaver
adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления: