Updating 'n' column values of table using dynamic query

Question

I've two tables, theta and convergence_table, each having same number of columns(7 columns here) (w0,w1,...w6; j0,j1,...j6). I need to update the 'w' values as wn=wn-jn. Here I'm updating the values of theta table with the help of a user-defined datatypes wt & jt(%rowtype).

select * into jt from convergence_table;                 
select * into wt from theta

update theta                            
    set w0 =  wt.w0-jt.j0, w1 =  wt.w1-jt.j1, w2 =  wt.w2-jt.j2,    
        w3 =  wt.w3-jt.j3, w4 =  wt.w4-jt.j4, w5 =  wt.w5-jt.j5, 
        w6 =  wt.w6-jt.j6;

But, now I've 'n+1' number of columns for both theta & convergence_table, so instead of writing the update statement and setting all the values from w0,w1,w2 .... wn. Is there a way to write a dynamic query to perform the updation for all the column values from w0 to wn.

I'm trying the following the code, but it isn't working ..

create or replace function sample(c int)
returns void as $$
declare jt convergence_table%rowtype; wt theta%rowtype;  
    query1 text:=''; query2 text:='';

begin

    select * into jt from convergence_table;                
    select * into wt from theta;

    for i in 0..n 
    loop
        query1 := query1 ||'w'||i||' = '||
        'wt.w'||i||' - jt.j'||i||',';
        end loop;

    query2 := trim(trailing ',' from query1);
    query2 := 'update theta set '||query2||';';
    execute query2;

return;
end;
$$ language plpgsql;

This gives me an error ...

ERROR:  missing FROM-clause entry for table "wt"
LINE 1: update theta set w0 = wt.w0 - jt.j0,w1 = wt.w1 - jt.j1,w2 = ...
                              ^
QUERY:  update theta set w0 = wt.w0 - jt.j0,w1 = wt.w1 - jt.j1,w2 = wt.w2 - jt.j2,w3 = wt.w3 - jt.j3,w4 = wt.w4 - jt.j4,w5 = wt.w5 - jt.j5,w6 = wt.w6 - jt.j6;
CONTEXT:  PL/pgSQL function sample(integer) line 20 at EXECUTE statement

********** Error **********

ERROR: missing FROM-clause entry for table "wt"
SQL state: 42P01
Context: PL/pgSQL function sample(integer) line 20 at EXECUTE statement

Can anyone help out with this?

Basically, this can be done easily in plpgsql. However, please clarify the task first. You want to *`UPDATE`*, but there is no definition of how to match rows. You make it seem like there is only a single row in each table? In an `UPDATE` there is typically at least one column that isn't changed. If you change all columns, you might as well `DELETE` & `INSERT` - but you still need to define *what* to delete ... — Erwin Brandstetter, Apr 16 '15 at 22:50
Yes, theta & covergence table have only single row with 'n' columns. Covergence table gets set of random values in each iteration & thus I've to update theta accordingly (Wn=W(n-1)-Jn). This is the scenario. i.e Convergence(J), theta (W) Initially (null), (5,4,4,3,3,4,4) After 1st iter. (1,2,3,2,1,4,3) -> (4,2,1,1,2,0,1) 2nd iteration (2,0,1,2,1,3,2) -> (2,2,0,-1,1,-3,-1) My theta table at the end is (2,2,0,-1,1,-3,-1). Usually I'll have 1000's of iterations. — Mourya Teja, Apr 19 '15 at 19:16
Sorry I'm unable to attach a image, since I'm only a beginner. @ErwinBrandstetter — Mourya Teja, Apr 19 '15 at 19:19
@ErwinBrandstetter, This is my email ID- mouryathelegend@gmail.com. Please send me your mail Id, So that I can explain you about the problem more precisely.. Thank you — Mourya Teja, Apr 19 '15 at 19:30
I appreciate the offer, but I'd rather keep this on-site. [Edit](http://stackoverflow.com/posts/29683602/edit) the question to clarify. — Erwin Brandstetter, Apr 20 '15 at 01:29

score 1 · Answer 1 · edited May 23 '17 at 11:51

CREATE OR REPLACE FUNCTION f_upd_dyn(_source regclass, _target regclass)
  RETURNS void AS
$func$
DECLARE
   source_cols text;
   target_cols text;
BEGIN
   SELECT INTO source_cols
          string_agg(quote_ident(attname), ', s.' ORDER BY attname)
   FROM   pg_attribute
   WHERE  attrelid = _source
   AND    NOT attisdropped              -- no dropped (dead) columns
   AND    attnum > 0;                   -- no system columns

   SELECT INTO target_cols
          string_agg(quote_ident(attname), ', ' ORDER BY attname)
   FROM   pg_attribute
   WHERE  attrelid = _target
   AND    NOT attisdropped              -- no dropped (dead) columns
   AND    attnum > 0;                   -- no system columns

   EXECUTE format('UPDATE %s t
                   SET   (%s) = (s.%s)  -- prepend 1st table qual s.
                   FROM  %s s' 
                  -- WHERE t.? = s.?    -- how to join source and target?
                 , _target::text, target_cols
                 , source_cols, _source::text
                 );
END
$func$ LANGUAGE plpgsql;

Call:

SELECT f_upd_dyn('convergence_table', 'theta');

Generates and executes code like:

UPDATE theta t SET (w1, w2, w3) = (s.j1, s.j2, s.j3)
FROM convergence_table s
-- note the missing WHERE condition!

SQL Fiddle.

You have been asking a similar question recently:

Using dynamic query + user defined datatype in Postgres

There are links to more explanation. Like this one:

Update multiple columns in a trigger function in plpgsql

score 0 · Answer 2 · answered Apr 16 '15 at 19:00

0

You could use the PL/pgsql EXECUTE command, building your query string from information_schema.columns.

answered Apr 16 '15 at 19:00

Politank-Z

3,653
3
24
28

Sorry,I'm new to PostgreSQL and not familiar with that, Is there a way to modify my above code and get the answer ?? @Politank-Z – Mourya Teja Apr 16 '15 at 19:29
http://stackoverflow.com/questions/9643859/postgres-missing-from-clause-entry-error-on-query-with-with-clause – Politank-Z Apr 16 '15 at 19:41
If I'm not wrong, using the from-clause here won't help, the wt & jt are user-defined types (rowtype). & this indeed would give me another error saying jt & wt doesn't exist. @Politank-Z – Mourya Teja Apr 16 '15 at 19:55
You could use PL/pgsql to compute the results of the subtractions into an array, and use the array to construct your update statement. – Politank-Z Apr 16 '15 at 20:09

Updating 'n' column values of table using dynamic query

2 Answers2