Re: Strange behaviour on function - Mailing list pgsql-general

From Lorusso Domenico
Subject Re: Strange behaviour on function
Date
Msg-id CAJMpnG64mNQYB16VgzeL7qJhUeRS7RUuHPMbfGzJ9rhmb16YOw@mail.gmail.com
Whole thread Raw
In response to Re: Strange behaviour on function  (Erik Wienhold <ewie@ewie.name>)
List pgsql-general
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.]

pgsql-general by date:

Previous
From: Erik Wienhold
Date:
Subject: Re: Strange behaviour on function
Next
From: Lorusso Domenico
Date:
Subject: Re: Strange behaviour on function