Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qH4FG-0002n2-5d for pgsql-general@arkaria.postgresql.org; Wed, 05 Jul 2023 15:16:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qH4FE-0005K9-22 for pgsql-general@arkaria.postgresql.org; Wed, 05 Jul 2023 15:16:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qH4FC-0005Jz-KN for pgsql-general@lists.postgresql.org; Wed, 05 Jul 2023 15:16:39 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qH4F4-002Brg-QW for pgsql-general@lists.postgresql.org; Wed, 05 Jul 2023 15:16:37 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id BEBE35C030B; Wed, 5 Jul 2023 11:16:28 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute6.internal (MEProxy); Wed, 05 Jul 2023 11:16:28 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1688570188; x=1688656588; bh=7/x7lNQ9tyQR1mIoTwoYX2UbEWIPVarD1Mu zbxgLxd8=; b=W77HXj2BEOivaaFedORrdoBpb1fYYadKzuOik3v7O/Ll9+UqL9T NW87PAO855eIuimRPAmtouRJ27GwX8AcMFsFyocE+9cZGyQ+SSBfyvNpoHoNKE3o bX6U9SYvtuNV5BJw0X7QC9VYKmvkBJen5XTcJYo/okDis8LGmKK8tzDdtsRFYwu7 +iOSNXae/L1G1bJmUQQP4dp1xvGfLZdc3AbPW7JFimhtk4TKZRzNVW5Z/8rB7oFn /+CEGEqKzdfmjLycJOvpAigFVw2oxLJZFElQkz09r3DC4heNafxjDQ7VrcC2pVMz fzeCftwYXjN8BTBl3puRbFgna2kpCnTMouw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1688570188; x= 1688656588; bh=7/x7lNQ9tyQR1mIoTwoYX2UbEWIPVarD1MuzbxgLxd8=; b=n 6eUwYeNhOj6m9pYB07SAcmw55eevVnqfJLo76/ffmACAhcFKpdgPVdkHGacDklsz NmIXWMhSHFVHhHZWt0zuG+zbDri9LBQWtQZFt/o9mgoipehc1K6amtxoQ6lt1SyD AFa234SteftY0HKSpIgF9tgtUCEXkkWZAX5r4XrcbmTiLKyRzvWda7GsRd6gNQ0+ Duz2AcuSMGzLpGB09J+330KsCA7HQRtPT4sFvdG8sh9dt+Ti+1+4+spl+EN8AmOj 1vBmsjfsr1jd8CYO7jQ3AZr/8IbVzoqClgbaW4uQHLI8ghsfIpwZCIC1rhnRvVaA A20XApfdODxp1X4HZUDHQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrudejgdegudcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfhfhfgjtgfgsehtkeertddtfeejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepleekgeejhfekteehfeduveetheeivdduheekudelteef vdffueehtddvgedtfeefnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrghenuc evlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgr nhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 5 Jul 2023 11:16:27 -0400 (EDT) Message-ID: <84a4f250-e12f-9dd8-bf97-c794f6b909f5@aklaver.com> Date: Wed, 5 Jul 2023 08:16:26 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: Strange behaviour on function To: Lorusso Domenico , pgsql-general@lists.postgresql.org References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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