Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to access the whole current record within update statement

Is it possible to reference/access/pass the current record within an update statement?

CREATE TABLE t1 (
  id serial PRIMARY KEY,
  name text
);

CREATE TABLE t2 (
  id serial PRIMARY KEY,
  name text,
  foo text
);

CREATE FUNCTION gen_t2_foo(_a t1, _b t2) RETURNS text AS $$
  SELECT _a.name || ' - ' || _b.name;
$$ LANGUAGE sql;

CREATE FUNCTION upd_t2(_min_id int, _max_id int, _a t1) RETURNS VOID AS $$
  UPDATE t2 SET
    foo = gen_f2_name(_a, ???) -- How to pass the current t2 record?
    WHERE id >= _min_id AND id <= _max_id;
$$ LANGUAGE sql;
like image 306
hooblei Avatar asked Nov 27 '25 07:11

hooblei


1 Answers

Just refer to the table:

create function upd_t2(_min_id int, _max_id int, _a t1)
returns void as $$
    update t2
    set foo = gen_t2_foo (_a, t2)
    where id >= _min_id and id <= _max_id;
$$ language sql;
like image 180
Clodoaldo Neto Avatar answered Nov 29 '25 20:11

Clodoaldo Neto



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!