pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pPL/pgSQL restriction on characters for copying types?
8+ messages / 3 participants
[nested] [flat]

* pPL/pgSQL restriction on characters for copying types?
@ 2024-02-26 15:51 Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 16:02 ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 16:35 ` Re: pPL/pgSQL restriction on characters for copying types? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 8+ messages in thread

From: Thiemo Kellner @ 2024-02-26 15:51 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi

My names can contain a special character (⠒), e.g. to separate the 
donator object from the name or the name from the type. Typically, I use 
this with foreign keys, e.g. table PARENT has the surrogate key ID, the 
table CHILD would then have the column PARENT⠒ID. That way, I can use 
the underscore to segment column names like yet_another_column_name. I 
do not like camel-case in that case because names do not have to be case 
sensitive.

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.

V⠒NODE_TYPE⠒NAME text;

Please find attached script files of objects directly involved in the 
trigger function.

Is there something, I am doing wrongly?

Kind Regards

Thiemo

Attachments:

NODE_GOOD⠒TR_B_IU_R.pg_sql (text/plain, 946B) download @echo Set up function NODE_GOOD⠒TR_B_IU_R; set search_path = snowrunner, public; create or replace function NODE_GOOD⠒TR_B_IU_R() returns trigger language plpgsql stable as $body$ declare -- V⠒NODE_TYPE⠒NAME text; V⠒NODE_TYPE⠒NAME NODE⠒V.NODE_TYPE⠒NAME%type := null; begin select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME from NODE⠒V where 1 = 1 and ID = new.NODE⠒ID and 1 = 1; if ( V⠒NODE_TYPE⠒NAME = 'Drop-off' and new.TASK_NAME is null) then raise exception 'A good connection to a drop-off node must have a task name!'; elsif ( V⠒NODE_TYPE⠒NAME != 'Drop-off' and new.TASK_NAME is not null) then raise exception 'A good connection to a non-drop-off node cannot have a task name!'; end if; return null; end; $body$; commit;
NODE⠒V.pg_sql (text/plain, 5.4K) download @echo Set view NODE⠒V up; create or replace view NODE⠒V as select N.NAME as NODE⠒NAME, -- N.QUANTITY, T.NAME as NODE_TYPE⠒NAME, M.MAP⠒NAME, M.MAP⠒CODE, M.REGION⠒NAME, M.REGION⠒COMPLETION_PERCENTATGE, A.CENTRICITY⠒NAME, A.DIRECTION⠒CODE, N.DESCRIPTION as NODE⠒DESCRIPTION, T.DESCRIPTION as NODE_TYPE⠒DESCRIPTION, M.MAP⠒DESCRIPTION, M.REGION⠒DESCRIPTION, A.AREA⠒DESCRIPTION, A.CENTRICITY⠒DESCRIPTION, A.DIRECTION⠒DESCRIPTION, N.ID, N.NODE_TYPE⠒ID, N.MAP⠒ID, M.REGION⠒ID, N.AREA⠒ID, A.CENTRICITY⠒ID, A.DIRECTION⠒ID from NODE N inner join NODE_TYPE T on N.NODE_TYPE⠒ID = T.ID inner join MAP⠒V M on N.MAP⠒ID = M.ID inner join AREA⠒V A on N.AREA⠒ID = A.ID; create or replace rule NODE⠒R_I as on insert to NODE⠒V do instead insert into NODE (MAP⠒ID, AREA⠒ID, NODE_TYPE⠒ID, NAME, -- QUANTITY, DESCRIPTION) values (/* MAP⠒I */ case -- ID given when new.MAP⠒ID is not null then new.MAP⠒ID -- name or code and region given when ( new.MAP⠒CODE is not null or new.MAP⠒NAME is not null) and ( new.REGION⠒ID is not null or new.REGION⠒NAME is not null) then (select ID from MAP⠒V where ( MAP⠒CODE = new.MAP⠒CODE or MAP⠒NAME = new.MAP⠒NAME) and ( REGION⠒ID = new.REGION⠒ID or REGION⠒NAME = new.REGION⠒NAME)) else null end, /* AREA⠒ID */ case -- ID given when new.AREA⠒ID is not null then new.AREA⠒ID -- name given when ( new.DIRECTION⠒CODE is not null or new.DIRECTION⠒ID is not null) and ( new.CENTRICITY⠒ID is not null or new.CENTRICITY⠒NAME is not null) then (select ID from AREA⠒V where ( DIRECTION⠒CODE = new.DIRECTION⠒CODE or DIRECTION⠒ID = new.DIRECTION⠒ID) and ( CENTRICITY⠒ID = new.CENTRICITY⠒ID or CENTRICITY⠒NAME = new.CENTRICITY⠒NAME)) else null end, /* NODE_TYPE⠒ID */ case -- ID given when new.NODE_TYPE⠒ID is not null then new.NODE_TYPE⠒ID -- name given when new.NODE_TYPE⠒NAME is not null then (select ID from NODE_TYPE where NAME = new.NODE_TYPE⠒NAME) else null end, /* NAME */ new.NODE⠒NAME, -- /* QUANTITY */ new.QUANTITY, /* DESCRIPTION */ new.NODE⠒DESCRIPTION); comment on rule NODE⠒R_I on NODE⠒V is 'We do not support filling the joined tables, thus they have to be filled already; i.e. no recursion'; create or replace trigger NODE⠒V⠒TR_B_DU_S before delete or update on NODE⠒V for each statement execute function REJECT_OPERATION(); comment on view NODE⠒V is ' $Header: svn+ssh://thiemo__sourceforge/p/snowrunner/code/trunk/code/postgresql/views/NODE%E2%A0%92V.pg_sql 2 2024-02-24 23:50:08Z thiemo $'; commit;
NODE_GOOD⠒V.pg_sql (text/plain, 4.6K) download @echo Set view NODE_GOOD⠒V up; create or replace view NODE_GOOD⠒V as select N.NODE⠒NAME, -- N.QUANTITY as QUANTITY_AVAILABLE_AT_NODE, G.GOOD⠒NAME, NG.QUANTITY, NG.TASK_NAME, N.CENTRICITY⠒NAME, N.DIRECTION⠒CODE, N.NODE_TYPE⠒NAME, N.MAP⠒NAME, N.MAP⠒CODE, N.REGION⠒NAME, G.GOOD_CLASS⠒NAME, G.SIZE, G.WEIGHT, NG.DESCRIPTION as NODE_GOOD⠒DESCRIPTION, N.NODE⠒DESCRIPTION, N.NODE_TYPE⠒DESCRIPTION, N.CENTRICITY⠒DESCRIPTION, N.DIRECTION⠒DESCRIPTION, N.AREA⠒DESCRIPTION, N.MAP⠒DESCRIPTION, N.REGION⠒DESCRIPTION, G.GOOD⠒DESCRIPTION, G.GOOD_CLASS⠒DESCRIPTION, NG.ID, NG.NODE⠒ID, NG.GOOD⠒ID, N.NODE_TYPE⠒ID, N.CENTRICITY⠒ID, N.DIRECTION⠒ID, N.AREA⠒ID, N.MAP⠒ID, N.REGION⠒ID, G.GOOD_CLASS⠒ID from NODE_GOOD NG inner join NODE⠒V N on NG.NODE⠒ID = N.ID inner join GOOD⠒V G on NG.GOOD⠒ID = G.ID; create or replace rule NODE_GOOD⠒R_I as on insert to NODE_GOOD⠒V do instead insert into NODE_GOOD (NODE⠒ID, GOOD⠒ID, QUANTITY, TASK_NAME, DESCRIPTION) values (/* NODE⠒ID */ case -- ID given when new.NODE⠒ID is not null then new.NODE⠒ID -- names or codes given when new.NODE⠒NAME is not null and ( new.MAP⠒NAME is not null or new.MAP⠒CODE is not null) and new.REGION⠒NAME is not null then (select ID from NODE⠒V where NODE⠒NAME = new.NODE⠒NAME and NODE_TYPE⠒NAME = 'Crafting zone' and ( MAP⠒NAME = new.MAP⠒NAME or MAP⠒CODE = new.MAP⠒CODE) and REGION⠒NAME = new.REGION⠒NAME) else null end, /* GOOD⠒ID */ case -- ID given when new.GOOD⠒ID is not null then new.GOOD⠒ID -- name given when new.GOOD⠒NAME is not null then (select ID from GOOD⠒V where GOOD⠒NAME = new.GOOD⠒NAME) else null end, /* QUANTITY */ new.QUANTITY, /* TASK_NAME */ new.TASK_NAME, /* DESCRIPTION */ new.NODE_GOOD⠒DESCRIPTION); comment on rule NODE_GOOD⠒R_I on NODE_GOOD⠒V is 'We do not support filling the joined tables, thus they have to be filled already; i.e. no recursion'; create or replace trigger NODE_GOOD⠒V⠒TR_B_DU_S before delete or update on NODE_GOOD⠒V for each statement execute function REJECT_OPERATION(); comment on column NODE_GOOD⠒V.QUANTITY is 'Number of the good that initially can be picked up or must be dropped off depending on the node type. For crafting zones it is a pick-up number.'; comment on view NODE_GOOD⠒V is ' $Header: svn+ssh://thiemo__sourceforge/p/snowrunner/code/trunk/code/postgresql/views/NODE_GOOD%E2%A0%92V.pg_sql 2 2024-02-24 23:50:08Z thiemo $'; commit;
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: pPL/pgSQL restriction on characters for copying types?
  2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-02-26 16:02 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
  1 sibling, 0 replies; 8+ messages in thread

From: Thiemo Kellner @ 2024-02-26 16:02 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Oh, I totally forgot to mention that I ran the scripts with DbVisualizer 
against a 16.1 (Debian 16.1-1.pgdg110+1) server using PostgreSQL JDBC 
Driver 42.6.0 .

Am 26.02.2024 um 16:51 schrieb Thiemo Kellner:
> Hi
> 
> My names can contain a special character (⠒), e.g. to separate the 
> donator object from the name or the name from the type. Typically, I use 
> this with foreign keys, e.g. table PARENT has the surrogate key ID, the 
> table CHILD would then have the column PARENT⠒ID. That way, I can use 
> the underscore to segment column names like yet_another_column_name. I 
> do not like camel-case in that case because names do not have to be case 
> sensitive.
> 
> 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.
> 
> V⠒NODE_TYPE⠒NAME text;
> 
> Please find attached script files of objects directly involved in the 
> trigger function.
> 
> Is there something, I am doing wrongly?
> 
> Kind Regards
> 
> Thiemo





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pPL/pgSQL restriction on characters for copying types?
  2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-02-26 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
  2024-02-26 16:53   ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  1 sibling, 1 reply; 8+ messages in thread

From: Tom Lane @ 2024-02-26 16:35 UTC (permalink / raw)
  To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; +Cc: pgsql-general@lists.postgresql.org

Thiemo Kellner <thiemo@gelassene-pferde.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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pPL/pgSQL restriction on characters for copying types?
  2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 16:35 ` Re: pPL/pgSQL restriction on characters for copying types? Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-02-26 16:53   ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 17:20     ` Re: pPL/pgSQL restriction on characters for copying types? Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-26 17:30     ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  0 siblings, 2 replies; 8+ messages in thread

From: Thiemo Kellner @ 2024-02-26 16:53 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

Am 26.02.2024 um 17:35 schrieb Tom Lane:
> Thiemo Kellner <thiemo@gelassene-pferde.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

Attachments:

  snowrunner.7z (application/x-compressed, 37.2K) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pPL/pgSQL restriction on characters for copying types?
  2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 16:35 ` Re: pPL/pgSQL restriction on characters for copying types? Tom Lane <tgl@sss.pgh.pa.us>
  2024-02-26 16:53   ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-02-26 17:20     ` Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2024-02-26 17:20 UTC (permalink / raw)
  To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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@gelassene-pferde.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.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pPL/pgSQL restriction on characters for copying types?
  2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 16:35 ` Re: pPL/pgSQL restriction on characters for copying types? Tom Lane <tgl@sss.pgh.pa.us>
  2024-02-26 16:53   ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-02-26 17:30     ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 17:42       ` Re: pPL/pgSQL restriction on characters for copying types? Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Thiemo Kellner @ 2024-02-26 17:30 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Shame on me. My bad. It was the order of installation that did not work. 
Sorry for that. I was mislead by the error message. If an object is 
missing I would not expect an invalid type name message.

Thanks

Am 26.02.2024 um 17:53 schrieb Thiemo Kellner:
> 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.
> 
> Am 26.02.2024 um 17:35 schrieb Tom Lane:
>> Thiemo Kellner <thiemo@gelassene-pferde.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





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pPL/pgSQL restriction on characters for copying types?
  2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 16:35 ` Re: pPL/pgSQL restriction on characters for copying types? Tom Lane <tgl@sss.pgh.pa.us>
  2024-02-26 16:53   ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 17:30     ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
@ 2024-02-26 17:42       ` Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-26 18:09         ` Re: pPL/pgSQL restriction on characters for copying types? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2024-02-26 17:42 UTC (permalink / raw)
  To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-general@lists.postgresql.org

On 2/26/24 09:30, Thiemo Kellner wrote:
> Shame on me. My bad. It was the order of installation that did not work. 
> Sorry for that. I was mislead by the error message. If an object is 
> missing I would not expect an invalid type name message.

For all the code knows it could be just a misspelling.

> 
> Thanks
> 
> Am 26.02.2024 um 17:53 schrieb Thiemo Kellner:
>> 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.
>>
>> Am 26.02.2024 um 17:35 schrieb Tom Lane:
>>> Thiemo Kellner <thiemo@gelassene-pferde.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.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pPL/pgSQL restriction on characters for copying types?
  2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 16:35 ` Re: pPL/pgSQL restriction on characters for copying types? Tom Lane <tgl@sss.pgh.pa.us>
  2024-02-26 16:53   ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 17:30     ` Re: pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
  2024-02-26 17:42       ` Re: pPL/pgSQL restriction on characters for copying types? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-26 18:09         ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2024-02-26 18:09 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-general@lists.postgresql.org

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 2/26/24 09:30, Thiemo Kellner wrote:
>> Shame on me. My bad. It was the order of installation that did not work. 
>> Sorry for that. I was mislead by the error message. If an object is 
>> missing I would not expect an invalid type name message.

> For all the code knows it could be just a misspelling.

I think Thiemo's got a point: "invalid type name" isn't the sort
of phrasing we'd normally use.  Compare

regression=# select 0::foo;
ERROR:  type "foo" does not exist

regression=# create function f() returns foo.bar%type as 'select 1' language sql;
ERROR:  relation "foo" does not exist

regression=# create function f() returns void language plpgsql as
$$declare x foo.bar%type; begin end$$;
ERROR:  syntax error at or near "%"
LINE 2: $$declare x foo.bar%type; begin end$$;
                           ^
CONTEXT:  invalid type name "foo.bar%type"

Digging in the plpgsql code, I notice that there's already a comment
complaining about how this is unhelpful:

     * If we have a simple or composite identifier, check for %TYPE and
     * %ROWTYPE constructs.  (Note that if plpgsql_parse_wordtype et al fail
     * to recognize the identifier, we'll fall through and pass the whole
     * string to parse_datatype, which will assuredly give an unhelpful
     * "syntax error".  Should we try to give a more specific error?)

which I believe I wrote not very long ago as part of an unrelated
change (digs ... yeah, see 5e8674dc8).  I'd not gone further than that
because the previous behavior was no better, but maybe it's time to
work harder.  The main problem is that this code doesn't know whether
the appropriate complaint is about a table not existing or a table
column not existing.  Maybe it's okay to let plpgsql_parse_wordtype
etc throw the error for themselves, though.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2024-02-26 18:09 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-26 15:51 pPL/pgSQL restriction on characters for copying types? Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-02-26 16:02 ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-02-26 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-26 16:53   ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-02-26 17:20     ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-26 17:30     ` Thiemo Kellner <thiemo@gelassene-pferde.biz>
2024-02-26 17:42       ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-26 18:09         ` Tom Lane <tgl@sss.pgh.pa.us>

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