Postgres Pro
Downloads
Home   >   mailing lists

Re: Function to set up variable inside it - Mailing list pgsql-general

From Łukasz Jarych
Subject Re: Function to set up variable inside it
Date
Msg-id DB7PR04MB53374486CC21C234F089B89BA1910@DB7PR04MB5337.eurprd04.prod.outlook.com
Whole thread Raw
In response to Re: Function to set up variable inside it  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-general
Tree view
Thank you Pavel!  I wilk set Up variable before  function and use it Inside function , best jacek

Pobierz aplikację Outlook dla systemu iOS
From: Pavel Stehule <pavel.stehule@gmail.com>
Sent: Wednesday, May 16, 2018 9:07:15 AM
To: Łukasz Jarych
Cc: Adrian Klaver; pgsql-general@postgresql.org >> PG-General Mailing List
Subject: Re: Function to set up variable inside it
 
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$

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-declarations.html


Best,
Jacek


--
Adrian Klaver
adrian.klaver@aklaver.com


pgsql-general by date:

Previous
From: Laurenz Albe
Date:
Subject: Re: sql function with empty row
Next
From: Michael Stephenson
Date:
Subject: Re: When use triggers?