postgresqlpostgres-plpython

How to update a Postgresql table using a composite type


I am trying to update a table using a composite key, and can't figure out the syntax. I have a table defined as:

create schema test;
create type test.ra_dec as (f1 double precision, f2 double precision);
create table test.pos_vel(
      xp double precision,
      yp double precision,
      xv double precision,
      yv double precision
);
insert into test.pos_vel(xp,yp,xv,yv) values
       (1,2,5,10),
       (2,3,10,20),
       (3,4,20,40);

I can verify my define type works:

gsh=# select cast( (100,200) as test.ra_dec);
    row    
-----------
 (100,200)
(1 row)

But I can't figure out how to update my table.

gsh=# update test.pos_vel set (xv,yv) = (select cast( (100,200) as test.ra_dec));
ERROR:  syntax error at or near "select"
LINE 1: update test.pos_vel set (xv,yv) = (select cast( (100,200) as...

I know for this trival example I can do it without using a composite type, but for speed reasons I want to define a function in pl/python and have it return a composite type, so I need to figure out the proper syntax.

Can anyone help please?


Solution

  • -- drop schema if exists test cascade;
    create schema test;
    create type test.ra_dec as (f1 double precision, f2 double precision);
    
    -- Your function here
    create function test.f() returns test.ra_dec language sql as $$
      select (100,200)::test.ra_dec $$;
    
    create table test.pos_vel(
          xp double precision,
          yp double precision,
          xv double precision,
          yv double precision
    );
    insert into test.pos_vel(xp,yp,xv,yv) values
           (1,2,5,10),
           (2,3,10,20),
           (3,4,20,40);
    
    update test.pos_vel set (xv,yv) = (select * from test.f());
    

    Update:

    Because sub-select for such cases was introduced in the PostgreSQL 9.5 it is not working in earlier versions. There are two workarounds:

    update test.pos_vel set (xv,yv) = ((test.f()).f1, (test.f()).f2);
    
    update test.pos_vel set (xv,yv) = (t.f1, t.f2) from (select * from test.f()) t;
    

    Tested on 8.4: http://dbfiddle.uk/?rdbms=postgres_8.4&fiddle=0a4927ed804b76442a69f8259cca1929