Re: select from composite type

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(dot)l76(at)gmail(dot)com <mailto:domenico(dot)l76(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> ha
> scritto:
>
> On Sun, Feb 4, 2024 at 5:39 PM Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com