Thread: Strange behaviour on function

Strange behaviour on function

From

Lorusso Domenico

Date:

05 July 2023, 15:23:44

Hello guys,

here a simple function

CREATE OR REPLACE FUNCTION bind_action(
sqlstr text,
hrec hstore)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
immutable PARALLEL SAFE
AS $BODY$
declare
_sqlstr text=sqlstr;
_k text;
_debug text;
begin
_debug= '--Start' || _sqlstr;
foreach _k in array akeys(hrec) loop
_debug =_debug || format($$
hstore: %s %s
sqlStr:$$, _k, hrec[_k]);
_sqlstr=replace(_sqlstr, ':'||_k||':', hrec[_k]);
_debug =_debug || _sqlstr;

end loop;

raise notice 'final %',_debug;
return _sqlstr;
end;
$BODY$;

and here a simple test

do
$$
declare
sqlstr text=':id::bignt,:surpa:,:disfa:';
hs hstore;
begin
hs['id']=789;
hs['disfa']='ssssssss';
raise notice '%',bind_action(sqlstr,hs);
end;
$$;

and it works.

But...

When I call this function in a function called by a trigger it down't work

_debug variable becomes null, also _sqlstr becomes null...

I can't identify what is going to happens...

any idea?

thanks

Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

Re: Strange behaviour on function

From

Erik Wienhold

Date:

05 July 2023, 16:53:11

> On 05/07/2023 14:23 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote:
>
> Hello guys,
> here a simple function
>
> CREATE OR REPLACE FUNCTION bind_action(
>  sqlstr text,
>  hrec hstore)
>  RETURNS text
>  LANGUAGE 'plpgsql'
>  COST 100
>  immutable PARALLEL SAFE
> AS $BODY$
> declare
>  _sqlstr text=sqlstr;
>  _k text;
>  _debug text;
> begin
>  _debug= '--Start' || _sqlstr;
>  foreach _k in array akeys(hrec) loop
>  _debug =_debug || format($$
>  hstore: %s %s
>  sqlStr:$$, _k, hrec[_k]);
>  _sqlstr=replace(_sqlstr, ':'||_k||':', hrec[_k]);
>  _debug =_debug || _sqlstr;
>
>  end loop;
>
>  raise notice 'final %',_debug;
>  return _sqlstr;
> end;
> $BODY$;
>
> and here a simple test
> do
> $$
> declare
>  sqlstr text=':id::bignt,:surpa:,:disfa:';
>  hs hstore;
> begin
>  hs['id']=789;
>  hs['disfa']='ssssssss';
>  raise notice '%',bind_action(sqlstr,hs);
> end;
> $$;
>
> and it works.
> But...
> When I call this function in a function called by a trigger it down't work
> _debug variable becomes null, also _sqlstr becomes null...

Does the hstore contain nulls?  Function replace returns null in that case.

Please show us the trigger, its function, and a reproducer.

--
Erik

Re: Strange behaviour on function

From

Adrian Klaver

Date:

05 July 2023, 18:16:26

On 7/5/23 05:23, Lorusso Domenico wrote:
> Hello guys,
> here a simple function
> 
> CREATE OR REPLACE FUNCTION bind_action(
> sqlstr text,
> hrec hstore)
>      RETURNS text
>      LANGUAGE 'plpgsql'
>      COST 100
>      immutable PARALLEL SAFE
> AS $BODY$
> declare
> _sqlstr text=sqlstr;
> _k text;
> _debug text;
> begin
> _debug= '--Start' || _sqlstr;
> foreach _k in array akeys(hrec) loop
> _debug =_debug || format($$
> hstore: %s %s
> sqlStr:$$, _k,  hrec[_k]);
> _sqlstr=replace(_sqlstr, ':'||_k||':', hrec[_k]);
> _debug =_debug ||  _sqlstr;
> 
> end loop;
> 
> raise notice 'final %',_debug;
> return _sqlstr;
> end;
> $BODY$;
> 
> and here a simple test
> do
> $$
> declare
> sqlstr text=':id::bignt,:surpa:,:disfa:';
> hs hstore;
> begin
> hs['id']=789;
> hs['disfa']='ssssssss';
> raise notice '%',bind_action(sqlstr,hs);
> end;
> $$;
> 
> and it works.
> But...
> When I call this function in a function called by a trigger it down't work
> _debug variable becomes null, also _sqlstr becomes null...

https://www.postgresql.org/docs/current/plpgsql-trigger.html

1)
"A trigger function must return either NULL or a record/row value having 
exactly the structure of the table the trigger was fired for."

2) I am not seeing where you use:

"TG_ARGV[]

     Data type array of text; the arguments from the CREATE TRIGGER 
statement. The index counts from 0. Invalid indexes (less than 0 or 
greater than or equal to tg_nargs) result in a null value."

So I don't see how sqlstr is being set?

> 
> I can't identify what is going to happens...
> 
> any idea?
> 
> 
> thanks
> 
> -- 
> Domenico L.
> 
> per stupire mezz'ora basta un libro di storia,
> io cercai di imparare la Treccani a memoria... [F.d.A.]

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Strange behaviour on function

From

Erik Wienhold

Date:

05 July 2023, 18:33:44

> On 05/07/2023 17:16 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> https://www.postgresql.org/docs/current/plpgsql-trigger.html
>
> 1)
> "A trigger function must return either NULL or a record/row value having
> exactly the structure of the table the trigger was fired for."
>
> 2) I am not seeing where you use:
>
> "TG_ARGV[]
>
>      Data type array of text; the arguments from the CREATE TRIGGER
> statement. The index counts from 0. Invalid indexes (less than 0 or
> greater than or equal to tg_nargs) result in a null value."
>
> So I don't see how sqlstr is being set?

Domenico did not provide the trigger definition, only function bind_action
which he calls from a trigger function.  Also bind_action cannot be a trigger
function because it does not return trigger.

--
Erik

Re: Strange behaviour on function

From

Lorusso Domenico

Date:

05 July 2023, 22:56:21

yes!!

I solved using quote_nullable on hrec[k].

I was convinced string || NULL --> string

but I'm wrong.

Thanks!!

Il giorno mer 5 lug 2023 alle ore 15:53 Erik Wienhold <ewie@ewie.name> ha scritto:

> On 05/07/2023 14:23 CEST Lorusso Domenico <domenico.l76@gmail.com> wrote:
>
> Hello guys,
> here a simple function
>
> CREATE OR REPLACE FUNCTION bind_action(
> sqlstr text,
> hrec hstore)
> RETURNS text
> LANGUAGE 'plpgsql'
> COST 100
> immutable PARALLEL SAFE
> AS $BODY$
> declare
> _sqlstr text=sqlstr;
> _k text;
> _debug text;
> begin
> _debug= '--Start' || _sqlstr;
> foreach _k in array akeys(hrec) loop
> _debug =_debug || format($$
> hstore: %s %s
> sqlStr:$$, _k, hrec[_k]);
> _sqlstr=replace(_sqlstr, ':'||_k||':', hrec[_k]);
> _debug =_debug || _sqlstr;
>
> end loop;
>
> raise notice 'final %',_debug;
> return _sqlstr;
> end;
> $BODY$;
>
> and here a simple test
> do
> $$
> declare
> sqlstr text=':id::bignt,:surpa:,:disfa:';
> hs hstore;
> begin
> hs['id']=789;
> hs['disfa']='ssssssss';
> raise notice '%',bind_action(sqlstr,hs);
> end;
> $$;
>
> and it works.
> But...
> When I call this function in a function called by a trigger it down't work
> _debug variable becomes null, also _sqlstr becomes null...

Does the hstore contain nulls? Function replace returns null in that case.

Please show us the trigger, its function, and a reproducer.

--
Erik

Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]

Re: Strange behaviour on function

From

Lorusso Domenico

Date:

05 July 2023, 23:03:39

Erik, Adrian,

The trigger function is very long and complex (and not yet well documented), but bind_action is a normal function callable everywhere, the problem as discovered by Erik was in the null value contained in the hstore.

Anyway, when the ecosystem of functions will work correctly I can share, so you can give me how improve :-D

Il giorno mer 5 lug 2023 alle ore 17:33 Erik Wienhold <ewie@ewie.name> ha scritto:

> On 05/07/2023 17:16 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> https://www.postgresql.org/docs/current/plpgsql-trigger.html
>
> 1)
> "A trigger function must return either NULL or a record/row value having
> exactly the structure of the table the trigger was fired for."
>
> 2) I am not seeing where you use:
>
> "TG_ARGV[]
>
> Data type array of text; the arguments from the CREATE TRIGGER
> statement. The index counts from 0. Invalid indexes (less than 0 or
> greater than or equal to tg_nargs) result in a null value."
>
> So I don't see how sqlstr is being set?

Domenico did not provide the trigger definition, only function bind_action
which he calls from a trigger function. Also bind_action cannot be a trigger
function because it does not return trigger.

--
Erik

Domenico L.

per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]