pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Lorusso Domenico <domenico.l76@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Strange behaviour on function
Date: Wed, 5 Jul 2023 08:16:26 -0700
Message-ID: <84a4f250-e12f-9dd8-bf97-c794f6b909f5@aklaver.com> (raw)
In-Reply-To: <CAJMpnG4byAZMOd-12Pxn7KcptJpi4MJCc-U-tS8KZNSE_6YkKg@mail.gmail.com>
References: <CAJMpnG4byAZMOd-12Pxn7KcptJpi4MJCc-U-tS8KZNSE_6YkKg@mail.gmail.com>

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






view thread (6+ messages)  latest in thread

Message-ID: <84a4f250-e12f-9dd8-bf97-c794f6b909f5@aklaver.com>
Permalink:  ../84a4f250-e12f-9dd8-bf97-c794f6b909f5@aklaver.com/
Also on:    postgresql.org/message-id/84a4f250-e12f-9dd8-bf97-c794f6b909f5@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, domenico.l76@gmail.com
  Subject: Re: Strange behaviour on function
  In-Reply-To: <84a4f250-e12f-9dd8-bf97-c794f6b909f5@aklaver.com>
  References: <CAJMpnG4byAZMOd-12Pxn7KcptJpi4MJCc-U-tS8KZNSE_6YkKg@mail.gmail.com> <84a4f250-e12f-9dd8-bf97-c794f6b909f5@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox