Обсуждение: select from composite type

On 2/4/24 14:50, Lorusso Domenico wrote:
> Hello guys,
> I'm trying to find out the equivalent behaviour of unnest, when I've got 
> just a composite type.
> 
> This is the statement is something like that (but more complex):
> 
>   _sqlUpdate text=$$
> with s as (
> select * from ($1)
> )
> update myView as q set
> (attribute_fullname, modify_user_id)
> =(s.attribute_fullname, $2)
> where  s.product_code=q.product_code
> and s.attribute_uid=q.attribute_uid
> $$;
> 
> And I use this command.
> execute _sqlUpdate using  attribute_list[_i], _modify_user_id;

Is the above in a plpgsql function?

> 
> attribute_list is  an array of composite type (with 20 fields).

I am trying to wrap my head around "array of composite type". Please 
provide an example.


> 
> If I use the unnest with the entire array the sql works, but I can't 
> find a way to treat the single record as a row of  a table.
> 
> 
> Any suggestions?
> 
> Bye and thanks'
> 
> Domenico

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 2/5/24 16:35, Lorusso Domenico wrote:
> ah ehm.. I solved, it was very easy but I believed it should use the 
> from clause...
> 
> execute 'select ($1).* ' using _attribute into _r;

Beat me to it

For the reason why it works:

https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

> 
> Il giorno mar 6 feb 2024 alle ore 01:01 Lorusso Domenico 
> <domenico.l76@gmail.com <mailto:domenico.l76@gmail.com>> ha scritto:
> 
>     here an example (the actual case in more complex, but the point it's
>     the same)
> 
>     do $$
>     declare
>     _attribute_list temp1.my_type[];
>     _attribute temp1.my_type;
> 
>     _r record;
>     begin
>     _attribute_list=array[row(1,'Hello') , row(2,'Goodbye')];
> 
>     _attribute= row(1,'Doh!!!!');
> 
>     raise notice '%', _attribute_list;
> 
>     for _r in execute 'select * from unnest($1) where foo=1' using
>     _attribute_list loop
>     raise notice '%', _r;
>     end loop;
> 
>     --Error
>     execute 'select * from $1' using _attribute into _r;
> 
>     raise notice '%', _r;
>     end;
>     $$;
> 
>     So I able to manage an array of complex type (why I use an array,
>     because in a previous answer the community suggest to me to use
>     an array to pass a list of information instead of temporary table),
>     but I can't do the same thing with just an element.
> 
>     Of course I can set an element as part of an array with just that
>     element but. it's sad...
> 
> 
>     Il giorno lun 5 feb 2024 alle ore 01:48 David G. Johnston
>     <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> ha
>     scritto:
> 
>         On Sun, Feb 4, 2024 at 5:39 PM Adrian Klaver
>         <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>         wrote:
> 
> 
>              >
>              > attribute_list is  an array of composite type (with 20
>             fields).
> 
>             I am trying to wrap my head around "array of composite
>             type". Please
>             provide an example.
> 
> 
>         ARRAY[ (1,2)::point, (3,4)::point ]::point[]
> 
>         The main problem is the concept of writing "from($1)" in any
>         query makes no sense, you cannot parameterize a from clause
>         directly like that.  You have to put the value somewhere an
>         expression is directly allowed.
> 
>         David J.
> 
> 
> 
>     -- 
>     Domenico L.
> 
>     per stupire mezz'ora basta un libro di storia,
>     io cercai di imparare la Treccani a memoria... [F.d.A.]
> 
> 
> 
> -- 
> 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