Re: pPL/pgSQL restriction on characters for copying types?

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 <thiemo(at)gelassene-pferde(dot)biz> 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(dot)klaver(at)aklaver(dot)com