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 Sun, Feb 4, 2024 at 5:39 PM Adrian Klaver <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.

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> ha scritto:

On Sun, Feb 4, 2024 at 5:39 PM Adrian Klaver <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.]

ah ehm.. I solved, it was very easy but I believed it should use the
from clause...

execute 'select ($1).* ' using _attribute into _r;

Il giorno mar 6 feb 2024 alle ore 01:01 Lorusso Domenico <
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> ha scritto:

On Sun, Feb 4, 2024 at 5:39 PM Adrian Klaver <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.]

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