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@xxxxxxxxxxx
