CODEWITHSUNDEEP
sqlpostgresqlsql-updateplpgsqldynamic-sql
Mourya Teja
Mourya Teja

Reputation: 23

Updating 'n' column values of table using dynamic query

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?

Upvotes: 0

Views: 1095

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

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:

There are links to more explanation. Like this one:

Upvotes: 1

Politank-Z
Politank-Z

Reputation: 3719

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

Upvotes: 0

Related Questions