Skip site navigation (1) Skip section navigation (2)

UPDATE... FROM - will ORDER BY not respected?

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: UPDATE... FROM - will ORDER BY not respected?
Date: 2009-04-28 17:48:03
Message-ID: gt7ffg$1nif$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
(FOR PG VERSION 8.3.6 running on Windows 2003 Server x64)

We have a function that assigns unique ID's (to use as row identifiers) to a 
table via an UPDATE using nextval(). This table is imported from another 
source, and there is a "sequencing" field to let the query know in which 
order to assign the row identifiers. (Please do not confuse the sequencing 
field with a sequence value from nextval())

The UPDATE command gets the order of the rows to update using a FROM clause, 
which in turn reads from a sub-query to get the rows in the order of "seq".

The problem is that the UPDATE is NOT behaving as if it is receiving the 
sequence identifiers in the order specified. In fact, it appears it is 
returned in REVERSE order (assigning id's in reverse order based on the 
values in seq)

Here is the essence of the query (further below you will find the full DDL 
code of the function).

UPDATE impt_table
SET id = nextval(''id_seq'')
FROM
          (SELECT seq
          FROM impt_table
          WHERE id IS NULL
          ORDER BY seq
          ) AS empty_ids
WHERE
          impt_table.seq = empty_ids.seq
          AND impt_table.id IS NULL;

Was I wrong in assuming that the UPDATE would respect the order of rows 
coming out of the sub-clause? Is there a better way to do this?

Thanks, Carlo


DDL CODE FOR FUNCTION
CREATE OR REPLACE FUNCTION "mdx_import"."impt_id_seed_from_impt_seq" (text) 
RETURNS boolean AS
$body$
/* New function body */
declare
   cmd varchar;
begin
   cmd =
      'update mdx_import.'||$1||'
       set impt_id = nextval(''mdx_import.impt_id_seq'')
       from
          (select impt_seq
          from mdx_import.'||$1||'
          where impt_id is null
          order by impt_seq
          ) as empty_impt_ids
       where
          '||$1||'.impt_seq = empty_impt_ids.impt_seq
          and '||$1||'.impt_id is null;';
   execute cmd;
   return true;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100; 


Responses

pgsql-general by date

Next:From: Christine PennerDate: 2009-04-28 17:56:59
Subject: Re: Restore Crashes Postgres
Previous:From: Allan KamauDate: 2009-04-28 16:47:28
Subject: Re: Restore Crashes Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group