Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Clean up some edge cases in plpgsql's %TYPE parsing.

  2. In plpgsql, allow %TYPE and %ROWTYPE to be followed by array decoration.

  1. PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Quan Zongliang <quanzongliang@yeah.net> — 2023-10-16T10:15:53Z

    
Implement TODO item:
PL/pgSQL
Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

As a first step, deal only with [], such as
xxx.yyy%TYPE[]
xxx%TYPE[]

It can be extended to support multi-dimensional and complex syntax in 
the future.


--
Quan Zongliang

  2. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Daniel Gustafsson <daniel@yesql.se> — 2023-10-16T11:53:11Z

    > On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net> wrote:

> Implement TODO item:
> PL/pgSQL
> Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

Cool!  While I haven't looked at the patch yet, I've wanted this myself many
times in the past when working with large plpgsql codebases.

> As a first step, deal only with [], such as
> xxx.yyy%TYPE[]
> xxx%TYPE[]
> 
> It can be extended to support multi-dimensional and complex syntax in the future.

Was this omitted due to complexity of implementation or for some other reason?

--
Daniel Gustafsson

  3. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-10-16T12:05:57Z

    po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson <daniel@yesql.se>
napsal:

> > On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net> wrote:
>
> > Implement TODO item:
> > PL/pgSQL
> > Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]
>
> Cool!  While I haven't looked at the patch yet, I've wanted this myself
> many
> times in the past when working with large plpgsql codebases.
>
> > As a first step, deal only with [], such as
> > xxx.yyy%TYPE[]
> > xxx%TYPE[]
> >
> > It can be extended to support multi-dimensional and complex syntax in
> the future.
>
> Was this omitted due to complexity of implementation or for some other
> reason?
>

There is no reason for describing enhancement. The size and dimensions of
postgresql arrays are dynamic, depends on the value, not on declaration.
Now, this information is ignored, and can be compatibility break to check
and enforce this info.


> --
> Daniel Gustafsson
>
>
>
>

  4. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Quan Zongliang <quanzongliang@yeah.net> — 2023-10-17T01:19:29Z

    Attached new patch
   More explicit error messages based on type.


On 2023/10/16 18:15, Quan Zongliang wrote:
> 
> 
> Implement TODO item:
> PL/pgSQL
> Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]
> 
> As a first step, deal only with [], such as
> xxx.yyy%TYPE[]
> xxx%TYPE[]
> 
> It can be extended to support multi-dimensional and complex syntax in 
> the future.
> 
> 
> -- 
> Quan Zongliang

  5. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Quan Zongliang <quanzongliang@yeah.net> — 2023-10-17T01:24:42Z

    Error messages still seem ambiguous.
    do not support multi-dimensional arrays in PL/pgSQL

Isn't that better?
    do not support multi-dimensional %TYPE arrays in PL/pgSQL


On 2023/10/17 09:19, Quan Zongliang wrote:
> 
> Attached new patch
>    More explicit error messages based on type.
> 
> 
> On 2023/10/16 18:15, Quan Zongliang wrote:
>>
>>
>> Implement TODO item:
>> PL/pgSQL
>> Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]
>>
>> As a first step, deal only with [], such as
>> xxx.yyy%TYPE[]
>> xxx%TYPE[]
>>
>> It can be extended to support multi-dimensional and complex syntax in 
>> the future.
>>
>>
>> -- 
>> Quan Zongliang

  6. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Quan Zongliang <quanzongliang@yeah.net> — 2023-10-17T01:29:25Z

    
On 2023/10/16 20:05, Pavel Stehule wrote:
> 
> 
> po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson <daniel@yesql.se 
> <mailto:daniel@yesql.se>> napsal:
> 
>      > On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net
>     <mailto:quanzongliang@yeah.net>> wrote:
> 
>      > Implement TODO item:
>      > PL/pgSQL
>      > Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]
> 
>     Cool!  While I haven't looked at the patch yet, I've wanted this
>     myself many
>     times in the past when working with large plpgsql codebases.
> 
>      > As a first step, deal only with [], such as
>      > xxx.yyy%TYPE[]
>      > xxx%TYPE[]
>      >
>      > It can be extended to support multi-dimensional and complex
>     syntax in the future.
> 
>     Was this omitted due to complexity of implementation or for some
>     other reason?
> 
Because of complexity.

> 
> There is no reason for describing enhancement. The size and dimensions 
> of postgresql arrays are dynamic, depends on the value, not on 
> declaration. Now, this information is ignored, and can be compatibility 
> break to check and enforce this info.
> 
Yes. I don't think it's necessary.
If anyone needs it, we can continue to enhance it in the future.

> 
>     --
>     Daniel Gustafsson
> 
> 
>

  7. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-10-17T04:15:06Z

    út 17. 10. 2023 v 3:30 odesílatel Quan Zongliang <quanzongliang@yeah.net>
napsal:

>
>
> On 2023/10/16 20:05, Pavel Stehule wrote:
> >
> >
> > po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson <daniel@yesql.se
> > <mailto:daniel@yesql.se>> napsal:
> >
> >      > On 16 Oct 2023, at 12:15, Quan Zongliang <quanzongliang@yeah.net
> >     <mailto:quanzongliang@yeah.net>> wrote:
> >
> >      > Implement TODO item:
> >      > PL/pgSQL
> >      > Incomplete item Allow handling of %TYPE arrays, e.g.
> tab.col%TYPE[]
> >
> >     Cool!  While I haven't looked at the patch yet, I've wanted this
> >     myself many
> >     times in the past when working with large plpgsql codebases.
> >
> >      > As a first step, deal only with [], such as
> >      > xxx.yyy%TYPE[]
> >      > xxx%TYPE[]
> >      >
> >      > It can be extended to support multi-dimensional and complex
> >     syntax in the future.
> >
> >     Was this omitted due to complexity of implementation or for some
> >     other reason?
> >
> Because of complexity.
>
> >
> > There is no reason for describing enhancement. The size and dimensions
> > of postgresql arrays are dynamic, depends on the value, not on
> > declaration. Now, this information is ignored, and can be compatibility
> > break to check and enforce this info.
> >
> Yes. I don't think it's necessary.
> If anyone needs it, we can continue to enhance it in the future.
>

I don't think it is possible to do it.  But there is another missing
functionality, if I remember well. There is no possibility to declare
variables for elements of array.

I propose syntax xxx.yyy%ELEMENTTYPE and xxx%ELEMENTTYPE

What do you think about it?

Regards

Pavel


> >
> >     --
> >     Daniel Gustafsson
> >
> >
> >
>
>

  8. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Quan Zongliang <quanzongliang@yeah.net> — 2023-10-17T09:20:27Z

    
On 2023/10/17 12:15, Pavel Stehule wrote:
> 
> 
> út 17. 10. 2023 v 3:30 odesílatel Quan Zongliang <quanzongliang@yeah.net 
> <mailto:quanzongliang@yeah.net>> napsal:
> 
> 
> 
>     On 2023/10/16 20:05, Pavel Stehule wrote:
>      >
>      >
>      > po 16. 10. 2023 v 13:56 odesílatel Daniel Gustafsson
>     <daniel@yesql.se <mailto:daniel@yesql.se>
>      > <mailto:daniel@yesql.se <mailto:daniel@yesql.se>>> napsal:
>      >
>      >      > On 16 Oct 2023, at 12:15, Quan Zongliang
>     <quanzongliang@yeah.net <mailto:quanzongliang@yeah.net>
>      >     <mailto:quanzongliang@yeah.net
>     <mailto:quanzongliang@yeah.net>>> wrote:
>      >
>      >      > Implement TODO item:
>      >      > PL/pgSQL
>      >      > Incomplete item Allow handling of %TYPE arrays, e.g.
>     tab.col%TYPE[]
>      >
>      >     Cool!  While I haven't looked at the patch yet, I've wanted this
>      >     myself many
>      >     times in the past when working with large plpgsql codebases.
>      >
>      >      > As a first step, deal only with [], such as
>      >      > xxx.yyy%TYPE[]
>      >      > xxx%TYPE[]
>      >      >
>      >      > It can be extended to support multi-dimensional and complex
>      >     syntax in the future.
>      >
>      >     Was this omitted due to complexity of implementation or for some
>      >     other reason?
>      >
>     Because of complexity.
> 
>      >
>      > There is no reason for describing enhancement. The size and
>     dimensions
>      > of postgresql arrays are dynamic, depends on the value, not on
>      > declaration. Now, this information is ignored, and can be
>     compatibility
>      > break to check and enforce this info.
>      >
>     Yes. I don't think it's necessary.
>     If anyone needs it, we can continue to enhance it in the future.
> 
> 
> I don't think it is possible to do it.  But there is another missing 
> functionality, if I remember well. There is no possibility to declare 
> variables for elements of array.
The way it's done now is more like laziness.

Is it possible to do that?
If the parser encounters %TYPE[][]. It can be parsed. Then let 
parse_datatype do the rest.

For example, partitioned_table.a%TYPE[][100][]. Parse the type 
name(int4) of partitioned_table.a%TYPE and add the following [][100][]. 
Passing "int4[][100][]" to parse_datatype will give us the array 
definition we want.

Isn't this code a little ugly?

> 
> I propose syntax xxx.yyy%ELEMENTTYPE and xxx%ELEMENTTYPE
> 
> What do you think about it?
No other relational database can be found with such an implementation. 
But it seems like a good idea. It can bring more convenience to write 
stored procedure.

> 
> Regards
> 
> Pavel
> 
> 
>      >
>      >     --
>      >     Daniel Gustafsson
>      >
>      >
>      >
>

  9. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-10-17T18:04:34Z

    Hi


> Isn't this code a little ugly?
>
> >
> > I propose syntax xxx.yyy%ELEMENTTYPE and xxx%ELEMENTTYPE
> >
> > What do you think about it?
> No other relational database can be found with such an implementation.
> But it seems like a good idea. It can bring more convenience to write
> stored procedure.
>

No other databases support arrays :-)

Regards

Pavel


> >
> > Regards
> >
> > Pavel
> >
> >
> >      >
> >      >     --
> >      >     Daniel Gustafsson
> >      >
> >      >
> >      >
> >
>
>

  10. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-11-20T09:33:00Z

    Hi

út 17. 10. 2023 v 3:20 odesílatel Quan Zongliang <quanzongliang@yeah.net>
napsal:

>
> Attached new patch
>    More explicit error messages based on type.
>
>
> On 2023/10/16 18:15, Quan Zongliang wrote:
> >
> >
> > Implement TODO item:
> > PL/pgSQL
> > Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]
> >
> > As a first step, deal only with [], such as
> > xxx.yyy%TYPE[]
> > xxx%TYPE[]
> >
> > It can be extended to support multi-dimensional and complex syntax in
> > the future.
> >
>

I did some deeper check:

- I don't like too much parser's modification (I am sending alternative own
implementation) - the SQL parser allows richer syntax, and for full
functionality is only few lines more

- original patch doesn't solve %ROWTYPE

(2023-11-20 10:04:36) postgres=# select * from foo;
┌────┬────┐
│ a  │ b  │
╞════╪════╡
│ 10 │ 20 │
│ 30 │ 40 │
└────┴────┘
(2 rows)

(2023-11-20 10:08:29) postgres=# do $$
declare v foo%rowtype[];
begin
  v := array(select row(a,b) from foo);
  raise notice '%', v;
end;
$$;
NOTICE:  {"(10,20)","(30,40)"}
DO

- original patch doesn't solve type RECORD
the error message should be more intuitive, although the arrays of record
type can be supported, but it probably needs bigger research.

(2023-11-20 10:10:34) postgres=# do $$
declare r record; v r%type[];
begin
  v := array(select row(a,b) from foo);
  raise notice '%', v;
end;
$$;
ERROR:  syntax error at or near "%"
LINE 2: declare r record; v r%type[];
                             ^
CONTEXT:  invalid type name "r%type[]"

- missing documentation

- I don't like using the word "partitioned" in the regress test name
"partitioned_table". It is confusing

Regards

Pavel



> >
> > --
> > Quan Zongliang

  11. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Quan Zongliang <quanzongliang@yeah.net> — 2023-11-23T12:27:51Z

    
On 2023/11/20 17:33, Pavel Stehule wrote:

> 
> 
> I did some deeper check:
> 
> - I don't like too much parser's modification (I am sending alternative 
> own implementation) - the SQL parser allows richer syntax, and for full 
> functionality is only few lines more
Agree.

> 
> - original patch doesn't solve %ROWTYPE
> 
> (2023-11-20 10:04:36) postgres=# select * from foo;
> ┌────┬────┐
> │ a  │ b  │
> ╞════╪════╡
> │ 10 │ 20 │
> │ 30 │ 40 │
> └────┴────┘
> (2 rows)
> 
> (2023-11-20 10:08:29) postgres=# do $$
> declare v foo%rowtype[];
> begin
>    v := array(select row(a,b) from foo);
>    raise notice '%', v;
> end;
> $$;
> NOTICE:  {"(10,20)","(30,40)"}
> DO
> 
two little fixes
1. spelling mistake
   ARRAY [ icons ] --> ARRAY [ iconst ]
2. code bug
   if (!OidIsValid(dtype->typoid)) --> if (!OidIsValid(array_typeid))


> - original patch doesn't solve type RECORD
> the error message should be more intuitive, although the arrays of 
> record type can be supported, but it probably needs bigger research.
> 
> (2023-11-20 10:10:34) postgres=# do $$
> declare r record; v r%type[];
> begin
>    v := array(select row(a,b) from foo);
>    raise notice '%', v;
> end;
> $$;
> ERROR:  syntax error at or near "%"
> LINE 2: declare r record; v r%type[];
>                               ^
> CONTEXT:  invalid type name "r%type[]"
> 
Currently only scalar variables are supported.
This error is consistent with the r%type error. And record arrays are 
not currently supported.
Support for r%type should be considered first. For now, let r%type[] 
report the same error as record[].
I prefer to implement it with a new patch.

> - missing documentation
My English is not good. I wrote it down, please correct it. Add a note 
in the "Record Types" documentation that arrays and "Copying Types" are 
not supported yet.

> 
> - I don't like using the word "partitioned" in the regress test name 
> "partitioned_table". It is confusing
fixed

> 
> Regards
> 
> Pavel

  12. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-11-23T19:39:38Z

    čt 23. 11. 2023 v 13:28 odesílatel Quan Zongliang <quanzongliang@yeah.net>
napsal:

>
>
> On 2023/11/20 17:33, Pavel Stehule wrote:
>
> >
> >
> > I did some deeper check:
> >
> > - I don't like too much parser's modification (I am sending alternative
> > own implementation) - the SQL parser allows richer syntax, and for full
> > functionality is only few lines more
> Agree.
>
> >
> > - original patch doesn't solve %ROWTYPE
> >
> > (2023-11-20 10:04:36) postgres=# select * from foo;
> > ┌────┬────┐
> > │ a  │ b  │
> > ╞════╪════╡
> > │ 10 │ 20 │
> > │ 30 │ 40 │
> > └────┴────┘
> > (2 rows)
> >
> > (2023-11-20 10:08:29) postgres=# do $$
> > declare v foo%rowtype[];
> > begin
> >    v := array(select row(a,b) from foo);
> >    raise notice '%', v;
> > end;
> > $$;
> > NOTICE:  {"(10,20)","(30,40)"}
> > DO
> >
> two little fixes
> 1. spelling mistake
>    ARRAY [ icons ] --> ARRAY [ iconst ]
> 2. code bug
>    if (!OidIsValid(dtype->typoid)) --> if (!OidIsValid(array_typeid))
>
>
> > - original patch doesn't solve type RECORD
> > the error message should be more intuitive, although the arrays of
> > record type can be supported, but it probably needs bigger research.
> >
> > (2023-11-20 10:10:34) postgres=# do $$
> > declare r record; v r%type[];
> > begin
> >    v := array(select row(a,b) from foo);
> >    raise notice '%', v;
> > end;
> > $$;
> > ERROR:  syntax error at or near "%"
> > LINE 2: declare r record; v r%type[];
> >                               ^
> > CONTEXT:  invalid type name "r%type[]"
> >
> Currently only scalar variables are supported.
> This error is consistent with the r%type error. And record arrays are
> not currently supported.
> Support for r%type should be considered first. For now, let r%type[]
> report the same error as record[].
> I prefer to implement it with a new patch.
>

ok


>
> > - missing documentation
> My English is not good. I wrote it down, please correct it. Add a note
> in the "Record Types" documentation that arrays and "Copying Types" are
> not supported yet.
>
> >
> > - I don't like using the word "partitioned" in the regress test name
> > "partitioned_table". It is confusing
> fixed
>

I modified the documentation a little bit - we don't need to extra propose
SQL array syntax, I think.
I rewrote regress tests - we don't need to test unsupported functionality
(related to RECORD).

- all tests passed

Regards

Pavel


>
> >
> > Regards
> >
> > Pavel

  13. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Quan Zongliang <quanzongliang@yeah.net> — 2023-11-24T01:11:59Z

    
On 2023/11/24 03:39, Pavel Stehule wrote:

> 
> I modified the documentation a little bit - we don't need to extra 
> propose SQL array syntax, I think.
> I rewrote regress tests - we don't need to test unsupported 
> functionality (related to RECORD).
> 
> - all tests passed
> 
I wrote two examples of errors:
   user_id users.user_id%ROWTYPE[];
   user_id users.user_id%ROWTYPE ARRAY[4][3];

Fixed.

> Regards
> 
> Pavel
> 
> 
>      >
>      > Regards
>      >
>      > Pavel
>

  14. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Pavel Stehule <pavel.stehule@gmail.com> — 2023-11-24T05:00:42Z

    pá 24. 11. 2023 v 2:12 odesílatel Quan Zongliang <quanzongliang@yeah.net>
napsal:

>
>
> On 2023/11/24 03:39, Pavel Stehule wrote:
>
> >
> > I modified the documentation a little bit - we don't need to extra
> > propose SQL array syntax, I think.
> > I rewrote regress tests - we don't need to test unsupported
> > functionality (related to RECORD).
> >
> > - all tests passed
> >
> I wrote two examples of errors:
>    user_id users.user_id%ROWTYPE[];
>    user_id users.user_id%ROWTYPE ARRAY[4][3];
>

there were more issues in this part - the name "user_id" is a bad name for
a composite variable.  I renamed it.
+ I wrote a test related to usage type without array support.

Now, I think so this simple patch is ready for committers

Regards

Pavel



> Fixed.
>
> > Regards
> >
> > Pavel
> >
> >
> >      >
> >      > Regards
> >      >
> >      > Pavel
> >

  15. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-04T21:02:07Z

    Pavel Stehule <pavel.stehule@gmail.com> writes:
> Now, I think so this simple patch is ready for committers

I pushed this with some editorialization -- mostly, rewriting the
documentation and comments.  I found that the existing docs for %TYPE
were not great.  There are two separate use-cases, one for referencing
a table column and one for referencing a previously-declared variable,
and the docs were about as clear as mud about explaining that.

I also looked into the problem Pavel mentioned that it doesn't work
for RECORD.  If you just write "record[]" you get an error message
that at least indicates it's an unsupported case:

regression=# do $$declare r record[]; begin end$$;
ERROR:  variable "r" has pseudo-type record[]
CONTEXT:  compilation of PL/pgSQL function "inline_code_block" near line 1

Maybe we could improve on that, but it would be a lot of work and
I'm not terribly excited about it.  However, %TYPE fails entirely
for both "record" and named composite types, and the reason turns
out to be just that plpgsql_parse_wordtype fails to handle the
PLPGSQL_NSTYPE_REC case.  So that's easily fixed.

I also wonder what the heck the last half of plpgsql_parse_wordtype
is for at all.  It looks for a named type, which means you can do

regression=# do $$declare x float8%type; begin end$$;
DO

but that's just stupid.  You could leave off the %TYPE and get
the same result.  Moreover, it is inconsistent because
plpgsql_parse_cwordtype has no equivalent behavior:

regression=# do $$declare x pg_catalog.float8%type; begin end$$;
ERROR:  syntax error at or near "%"
LINE 1: do $$declare x pg_catalog.float8%type; begin end$$;
                                        ^
CONTEXT:  invalid type name "pg_catalog.float8%type"

It's also undocumented and untested (the code coverage report
shows this part is never reached).  So I propose we remove it.

That leads me to the attached proposed follow-on patch.

Another thing we could think about, but I've not done it here,
is to make plpgsql_parse_wordtype and friends throw error
instead of just returning NULL when they don't find the name.
Right now, if NULL is returned, we end up passing the whole
string to parse_datatype, leading to unhelpful errors like
the one shown above.  We could do better than that I think,
perhaps like "argument of %TYPE is not a known variable".

			regards, tom lane

  16. Re: PL/pgSQL: Incomplete item Allow handling of %TYPE arrays, e.g. tab.col%TYPE[]

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-01-05T04:57:31Z

    čt 4. 1. 2024 v 22:02 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:

> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > Now, I think so this simple patch is ready for committers
>
> I pushed this with some editorialization -- mostly, rewriting the
> documentation and comments.  I found that the existing docs for %TYPE
> were not great.  There are two separate use-cases, one for referencing
> a table column and one for referencing a previously-declared variable,
> and the docs were about as clear as mud about explaining that.
>
> I also looked into the problem Pavel mentioned that it doesn't work
> for RECORD.  If you just write "record[]" you get an error message
> that at least indicates it's an unsupported case:
>
> regression=# do $$declare r record[]; begin end$$;
> ERROR:  variable "r" has pseudo-type record[]
> CONTEXT:  compilation of PL/pgSQL function "inline_code_block" near line 1
>
> Maybe we could improve on that, but it would be a lot of work and
> I'm not terribly excited about it.  However, %TYPE fails entirely
> for both "record" and named composite types, and the reason turns
> out to be just that plpgsql_parse_wordtype fails to handle the
> PLPGSQL_NSTYPE_REC case.  So that's easily fixed.
>
> I also wonder what the heck the last half of plpgsql_parse_wordtype
> is for at all.  It looks for a named type, which means you can do
>
> regression=# do $$declare x float8%type; begin end$$;
> DO
>
> but that's just stupid.  You could leave off the %TYPE and get
> the same result.  Moreover, it is inconsistent because
> plpgsql_parse_cwordtype has no equivalent behavior:
>
> regression=# do $$declare x pg_catalog.float8%type; begin end$$;
> ERROR:  syntax error at or near "%"
> LINE 1: do $$declare x pg_catalog.float8%type; begin end$$;
>                                         ^
> CONTEXT:  invalid type name "pg_catalog.float8%type"
>
> It's also undocumented and untested (the code coverage report
> shows this part is never reached).  So I propose we remove it.
>
> That leads me to the attached proposed follow-on patch.
>
> Another thing we could think about, but I've not done it here,
> is to make plpgsql_parse_wordtype and friends throw error
> instead of just returning NULL when they don't find the name.
> Right now, if NULL is returned, we end up passing the whole
> string to parse_datatype, leading to unhelpful errors like
> the one shown above.  We could do better than that I think,
> perhaps like "argument of %TYPE is not a known variable".
>

+1

Regards

Pavel

>
>                         regards, tom lane
>
>