Content-Type: text/plain;
  charset="iso-8859-1"
From: "D'Arcy J.M. Cain" <darcy@druid.net>
Reply-To: pgsql-sql@postgresql.org
To: "Matthew Nuzum" <cobalt@bearfruit.org>,
	<pgsql-sql@postgresql.org>
Subject: Re: cannot create function that uses variable table name
Date: Fri, 17 Jan 2003 04:48:29 -0500
User-Agent: KMail/1.4.3
References: <001d01c2bdd9$191b07f0$6700a8c0@mattspc>
In-Reply-To: <001d01c2bdd9$191b07f0$6700a8c0@mattspc>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200301170448.29838.darcy@druid.net>

On Thursday 16 January 2003 22:32, Matthew Nuzum wrote:
> I have a number of tables in my database that use the concept of
> “display order”, which is a field that can be used in an order by clause
> to dictate what order the results should come out in.
>  
> I thought I would be crafty and devise a function that would always
> return the highest numbered item in the table.  But it doesn’t work.  It
> always gives me a parse error at $1.  Here’s the function:

I may be wrong but aren't you trying to do something like this?

INSERT INTO files (accountid, filename, dsply_order)
  VALUES ('account2', 'Testing',
    (SELECT COALESCE(MAX(dsply_order), 0) + 1 FROM files 
      WHERE accountid = 'account2'));

Alternatively, assuming that fileid is a serial number, why not just use that 
in your order by clause.  I assume that you want something like this.

SELECT * FROM files WHERE accountid = 'account2' ORDER BY dsply_order;

This should give you exactly the same result:

SELECT * FROM files WHERE accountid = 'account2' ORDER BY fileid.

It all depends on what problem exactly you are trying to solve of course.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.