Postgres Pro
Downloads
Home   >   mailing lists

Thread: row constructors

row constructors

From
Sim Zacks
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Is there any way to insert a row constructor into the table of the same
type?

For example:
create table a1(id int, f1 text);
create table a2(id int, f2 a1);

insert into a1 select f2 from a2;

This doesn't work, I am looking for a way to accomplish this functionality.

Thank you
Sim
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkmT8ukACgkQjDX6szCBa+oI/wCghmqMF+FyNl1xz3E6xQSIr4ll
r04An05goEOsaTh+Ni5CEC9zjFRAqPCd
=uXgD
-----END PGP SIGNATURE-----

Re: row constructors

From
Sim Zacks
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Never mind. I found an old post.
I just needed to do:
insert into a1 select (f2).* from a2;

I didn't find it the first time I searched because I was looking for row
constructors, and the post I found used the term composite value.

Sim

Sim Zacks wrote:
> Is there any way to insert a row constructor into the table of the same
> type?
>
> For example:
> create table a1(id int, f1 text);
> create table a2(id int, f2 a1);
>
> insert into a1 select f2 from a2;
>
> This doesn't work, I am looking for a way to accomplish this functionality.
>
> Thank you
> Sim
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkmT8+4ACgkQjDX6szCBa+oqLgCgw2AZjkhiaWGNYpvICTHPkEii
rIsAoO3fYRxh5+Ohws2+IZG2RzO8Q9B6
=Gt11
-----END PGP SIGNATURE-----

Re: row constructors

From
Merlin Moncure
Date:
On Thu, Feb 12, 2009 at 5:03 AM, Sim Zacks <sim@compulab.co.il> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Never mind. I found an old post.
> I just needed to do:
> insert into a1 select (f2).* from a2;
>
> I didn't find it the first time I searched because I was looking for row
> constructors, and the post I found used the term composite value.

I'm scheming to get that fixed.  The main reason is that while the
insert workaround works, there is no similar workaround for 'update'.

merlin

Re: row constructors

From
Rory Campbell-Lange
Date:
On 12/02/09, Merlin Moncure (mmoncure@gmail.com) wrote:
> On Thu, Feb 12, 2009 at 5:03 AM, Sim Zacks <sim@compulab.co.il> wrote:
> > Never mind. I found an old post.
> > I just needed to do:
> > insert into a1 select (f2).* from a2;
> >
> > I didn't find it the first time I searched because I was looking for row
> > constructors, and the post I found used the term composite value.
>
> I'm scheming to get that fixed.  The main reason is that while the
> insert workaround works, there is no similar workaround for 'update'.

Do you mean that the currently unsupported behaviour

  UPDATE accounts SET (contact_last_name, contact_first_name) =
    (SELECT last_name, first_name FROM salesmen
     WHERE salesmen.id = accounts.sales_id);

will be fixed? (with reference to http://www.postgresql.org/docs/8.3/static/sql-update.html#AEN61082)

Rory

Re: row constructors

From
Merlin Moncure
Date:
On Thu, Feb 12, 2009 at 11:38 AM, Rory Campbell-Lange
<rory@campbell-lange.net> wrote:
> On 12/02/09, Merlin Moncure (mmoncure@gmail.com) wrote:
>> On Thu, Feb 12, 2009 at 5:03 AM, Sim Zacks <sim@compulab.co.il> wrote:
>> > Never mind. I found an old post.
>> > I just needed to do:
>> > insert into a1 select (f2).* from a2;
>> >
>> > I didn't find it the first time I searched because I was looking for row
>> > constructors, and the post I found used the term composite value.
>>
>> I'm scheming to get that fixed.  The main reason is that while the
>> insert workaround works, there is no similar workaround for 'update'.
>
> Do you mean that the currently unsupported behaviour
>
>  UPDATE accounts SET (contact_last_name, contact_first_name) =
>    (SELECT last_name, first_name FROM salesmen
>     WHERE salesmen.id = accounts.sales_id);

no, but that would also be nice. I mean,
update account set account = (123, 'a', 1)::account where account_id = 123;

merlin