Strange behaviour on function
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']=''; 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
> On 05/07/2023 14:23 CEST 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']=''; > 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
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']=''; 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.kla...@aklaver.com
Re: Strange behaviour on function
> On 05/07/2023 17:16 CEST Adrian Klaver 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
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 ha scritto: > > On 05/07/2023 14:23 CEST 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']=''; > > 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
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 ha scritto: > > On 05/07/2023 17:16 CEST Adrian Klaver > 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.]
