Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1reeeV-00CeD3-Hl for pgsql-general@arkaria.postgresql.org; Mon, 26 Feb 2024 17:20:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1reeeU-00FPXC-38 for pgsql-general@arkaria.postgresql.org; Mon, 26 Feb 2024 17:20:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1reeeS-00FPWg-6i for pgsql-general@lists.postgresql.org; Mon, 26 Feb 2024 17:20:30 +0000 Received: from wout3-smtp.messagingengine.com ([64.147.123.19]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1reeeO-001DXO-9m for pgsql-general@lists.postgresql.org; Mon, 26 Feb 2024 17:20:28 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 5A1B63200A57; Mon, 26 Feb 2024 12:20:21 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute6.internal (MEProxy); Mon, 26 Feb 2024 12:20:21 -0500 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:subject:subject:to:to; s=fm3; t=1708968020; x=1709054420; bh=b96r9UcrJJ8dUXKOzDqwyMe+xyTFvy4TASqLwxAt3iU=; b= bvxI6j5OcIY+gvKV3SGIpTiC7wgSPNeVdCYB9hj1SrajIVczsJg91uXggRQVKEKy nnnoy8H/kdwLgM/EwiWCey00z3mK2Vu9B8BF1AwfC/alTg3Hu2/Y7cWECL9D/YKl 8Ob+zzDUbpZop6LdDpIEeraHJaheHHE1JvOq/uhdrtsg2Pc6T2H0y1kb0oVz5VsD tN97keBFDtWQuOZyQC0+WhxFZKXpCgTEaHQuzH+VB2UvMmQqISE2mimX3pfJ9IKK 50i1OLeBjNekpyd3SG1gsbI03EfstxAZAq0QTrrjY292EP5pshi0qOVrAg/xLV+6 kzxjJG2FdiZ9bQtjvEugYQ== 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:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1708968020; x= 1709054420; bh=b96r9UcrJJ8dUXKOzDqwyMe+xyTFvy4TASqLwxAt3iU=; b=p V59LNYALGgFeOAAy3Qo/PLrlTVOKqhlnE4H4zrNxBkdnQupzvkosMKFIE6MTHoPm WNJZYaSjP8TJecMJda8lhE6ndWXfURA9B74f3nnBrAQ1cnC9qFdQYN/OcHAh88QG kN1I3+sWFHLFpwNRgHHiJsIYQuQeHJY3cd/npooWPNTbjfT7T1AKIym2oWJlykss fAyxsakKoE+Xplp0HAt9z5icChAeRsHhv0gH9Vf+XTkDj5Qde6b9QQ8GeTeK3XIS 8wheybLSLYIgjhgVTLJ0t4NPL22GMc+5rDrKGdNtz/xbB62Htbn/LF4ocgsdSHlX t+8QK4YE+prRofn5pRFMw== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrgedvgdeljecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpefkffggfgfuvfhfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughrihgr nhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh eqnecuggftrfgrthhtvghrnhepffelgeeifefgveduhedthfekuedtffejveegffegjeev tdehgfduieetfeehjeehnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrg hilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 26 Feb 2024 12:20:20 -0500 (EST) Message-ID: <380ddcc8-6158-49fd-b580-e349f71e2976@aklaver.com> Date: Mon, 26 Feb 2024 09:20:19 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pPL/pgSQL restriction on characters for copying types? Content-Language: en-US To: Thiemo Kellner , "pgsql-general@lists.postgresql.org" References: <88b574f4-cc08-46c5-826b-020849e5a356@gelassene-pferde.biz> <1812333.1708965337@sss.pgh.pa.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 2/26/24 08:53, Thiemo Kellner wrote: > Thanks for the hint and care. The install script has a set statement > already and I now added the search_path clause to no avail. Please find > the entire code attached and a screenshot from the error. I quick test: create table type_test(NODE_TYPE⠒NAME text); \d type_test Table "public.type_test" Column | Type | Collation | Nullable | Default ----------------+------+-----------+----------+--------- node_type⠒name | text | | CREATE OR REPLACE FUNCTION public.type_test_fnc() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE type_name type_test.node_type⠒name%TYPE :=NULL; BEGIN RAISE NOTICE 'TEST'; END; $function$ select type_test_fnc(); NOTICE: TEST type_test_fnc --------------- Shows that it is not the name itself that is the problem, at least in my case. I'm going to say it as Tom Lane said, there is a search_path issue. I tried to follow all the set search_path calls in your code and got lost as to where that ended up. I would try a simple test case, using psql, like above to verify that it is not the name in your case either. Assuming that works then you will need to track down what the actual search_path is when you run the function. > > Am 26.02.2024 um 17:35 schrieb Tom Lane: >> Thiemo Kellner writes: >>> However, I want to create a (trigger) function to impose data >>> consistency. For that purpose, I try to copy the data type of a PL/pgSQL >>> variable from the base object, a view in that case. Trying so, I get the >>> following error on installation of the function. >> >>> V⠒NODE_TYPE⠒NAME                NODE⠒V.NODE_TYPE⠒NAME%type := null; >> >>> Syntax error at "%" … invalid type name. If I use the actual type of the >>> column, all is fine. >> >> FWIW, I couldn't reproduce this with the fragmentary scripts you >> provided.  I suspect the problem is not about the special characters >> in the names, rather about search_path not including the NODE⠒V view. >> Consider schema-qualifying the view name, or attaching a "SET >> search_path" clause to the function. >> >>             regards, tom lane -- Adrian Klaver adrian.klaver@aklaver.com