Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 73EC99FBCA2 for ; Wed, 25 Jul 2007 15:46:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 95417-10 for ; Wed, 25 Jul 2007 15:46:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.179]) by postgresql.org (Postfix) with ESMTP id 7A3C69FBC9C for ; Wed, 25 Jul 2007 15:46:07 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id k40so355657wah for ; Wed, 25 Jul 2007 11:46:06 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; b=GufhxCIBt3SpmkC2/oRi/sD2yI/9NzwJOEsbx9WvRggfQW9dEJEjCdb/rfzQ+3KsMy5z64c7ZGhnWX5XA3yyZuzIxAHzla9wAAachl0SE3GITSUl0ZPtQ/qvCKRh+8B3WguPOvX7fh7bKtgsf9DVp2EBaquLnjM27ybxUrZ1EYE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=Ygqe1iR8GbYieswmqST+aBdlIwor8a7YuQTo3N+LFJV4k/vw+78etKPbKMnnW1VruND1aGkfxRcxTlhBWy2jHUMVrphSJTSigfLfgFGWeMKxGeUo6ie/Can3j8Zm0tLl1xyax27vuTpUejScDchkmcUDlplTHibqQCzw4tMoAWM= Received: by 10.114.24.1 with SMTP id 1mr908960wax.1185389165680; Wed, 25 Jul 2007 11:46:05 -0700 (PDT) Received: by 10.114.255.18 with HTTP; Wed, 25 Jul 2007 11:46:05 -0700 (PDT) Message-ID: <266b12630707251146q8c96d4an125d6835f0a236d0@mail.gmail.com> Date: Wed, 25 Jul 2007 15:46:05 -0300 From: "Eduardo Ferreira" To: pgsql-admin@postgresql.org Subject: PL/SQL dynamic update error MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_137921_2518095.1185389165639" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/285 X-Sequence-Number: 26266 ------=_Part_137921_2518095.1185389165639 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a table with informations that change every month, and I have to update the monthly data in a new column. To do that, I try to find out wich months are being trated and I perform an update in my table for every month. So what I'm trying to do is to change the month column for a fixed name, perform my selects and updates in the renamed column and change the name back for its old value. Then I do exactly the same thing for another month. It would be something like this: FOR month IN select month from monht_values LOOP v_command: = 'ALTER TABLE my_table RENAME COLUMN m' ||month ||' TO month_value;'; EXECUTE v_command; UPDATE my_table SET month_value = new_value WHERE key = my_key; v_command: = 'ALTER TABLE my_table RENAME COLUMN month_value TO m' ||month ||';'; EXECUTE v_command; END LOOP; The query works just fine, but the updates are made allways in the same column. Even when I change the name, PostgreSQL doesn't recognize it, and all the updates are performed in the same first column name. It seems like it stores the column information in a way I can't change the it. Any ideas on how to fix it? ------=_Part_137921_2518095.1185389165639 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I have a table with informations that change every month, and I have to update the monthly data in a new column. To do that, I try to find out wich months are being trated and I perform an update in my table for every month.

So what I'm trying to do is to change the month column for a fixed name, perform my selects and updates in the renamed column and change the name back for its old value. Then I do exactly the same thing for another month. It would be something like this:

FOR month IN select month from monht_values LOOP
    v_command: = 'ALTER TABLE my_table
    RENAME COLUMN m'
    ||month
    ||' TO month_value;';

    EXECUTE v_command;

    UPDATE my_table
    SET month_value = new_value
    WHERE key = my_key;

    v_command: = 'ALTER TABLE my_table
    RENAME COLUMN month_value TO m'
    ||month
    ||';';
    EXECUTE v_command;

END LOOP;


The query works just fine, but the updates are made allways in the same column. Even when I change the name, PostgreSQL doesn't recognize it, and all the updates are performed in the same first column name. It seems like it stores the column information in a way I can't change the it.

Any ideas on how to fix it?
------=_Part_137921_2518095.1185389165639--