Function to set up variable inside it

Started by Łukasz Jarychalmost 8 years ago6 messagesgeneral

Jump to latest

Łukasz Jarych

jaryszek@gmail.com

almost 8 years ago

Hi Guys,

I am using postgres 10.3 (or 4?).
IT is possible to set up variable inside function?

Best,
Jacek

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Łukasz Jarych (#1)

Re: Function to set up variable inside it

2018-05-15 14:28 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com>:

Hi Guys,

I am using postgres 10.3 (or 4?).
IT is possible to set up variable inside function?

I don't understand to the question. What do you think?

Regards

Pavel

Show quoted text

Best,
Jacek

Adrian Klaver

adrian.klaver@aklaver.com

almost 8 years ago

In reply to: Łukasz Jarych (#1)

Re: Function to set up variable inside it

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

Łukasz Jarych

jaryszek@gmail.com

almost 8 years ago

In reply to: Adrian Klaver (#3)

Re: Function to set up variable inside it

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

2018-05-15 14:58 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.com>:

Show quoted text

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

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Łukasz Jarych (#4)

Re: Function to set up variable inside it

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

Show quoted text

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

Łukasz Jarych

jaryszek@gmail.com

almost 8 years ago

In reply to: Pavel Stehule (#5)

Re: Function to set up variable inside it

Thank you Pavel! I wilk set Up variable before function and use it Inside function , best jacek

Pobierz aplikację Outlook dla systemu iOS<https://aka.ms/o0ukef>
________________________________
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

2018-05-16 6:52 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com<mailto: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<mailto: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<mailto:adrian.klaver@aklaver.com>