How to update a Postgresql table using a composite type

Question

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?

from your table definition there is no use of the type `test.ra_dec` :) `update test.pos_vel set xv = 100, yv=200` should work for you fine. Or edit the question and reflect what you are trying to achieve. — Ilya Dyoshin, May 02 '17 at 17:20
I said I wanted to know the syntax of updating from a composite type, since I want to have a function returning a composite type. — Greg Hennessy, May 02 '17 at 17:26

Abelisto · Accepted Answer · 2017-05-02T22:18:17.210

1

-- 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

edited May 02 '17 at 22:18

answered May 02 '17 at 18:16

Abelisto

14,826
2
33
41

Using psql 9.2.18 your last line gives me an error: gsh=# update test.pos_vel set (xv,yv) = (select * from test.f()); ERROR: syntax error at or near "select" LINE 1: update test.pos_vel set (xv,yv) = (select * from test.f()); ^ – Greg Hennessy May 02 '17 at 21:51
Still not working. `create type gsh.ra_dec as (ra double precision, de double precision); create function gsh.transform_cat() RETURNS ra_dec AS $$ -- function code deleted to save space. $$ LANGUAGE plpythonu stable; update gsh.cstar_ir_temp1 set (ra_2019,de_2019) = (tmp.ra, tmp.de) from (select gsh.transform_cat(1::smallint,2015.,ra,de,pmra,pmde, 2019.) from gsh.cstar_ir_temp1 ) as tmp where flg=1; ERROR: column tmp.ra does not exist LINE 2: (tmp.ra, tmp.de) from (select` – Greg Hennessy May 16 '17 at 17:26
@GregHennessy Yes, because your `select` returns single column of composite type instead of several columns of simple types (just try to execute it independently, without `update` and you will get something like `(..., ...)`). Try `... from (select (gsh.transform_cat(1::smallint,2015.,ra,de,pmra,pmde, 2019.)).* from ...` or move your function to the `from` clause. – Abelisto May 16 '17 at 17:30
`db=# (select gsh.transform_cat(1::smallint,2015.,ra,de,pmra,pmde, 2019.) from gsh.cstar_ir_temp1 ) limit 1; ` yields ` transform_cat ------------------------------- (54.0303567019,17.7522358896) (1 row) ` – Greg Hennessy May 16 '17 at 17:36
I've added the parens around the call to gsh.transform_cat() and the .* like you said, and it *seems* to work so far. It least it didn't throw an error. *crosses finger* Thank you for your help. – Greg Hennessy May 16 '17 at 17:37
I wish I could edit comments to show the code as easily as I can in the main post. – Greg Hennessy May 16 '17 at 17:37

How to update a Postgresql table using a composite type

1 Answers1