From: D'Arcy J.M. Cain <darcy@druid.net>
To: Matthew Nuzum <cobalt@bearfruit.org>
To: pgsql-sql@postgresql.org
Subject: Re: cannot create function that uses variable table name
Date: Fri, 17 Jan 2003 04:48:29 -0500
Message-ID: <200301170448.29838.darcy@druid.net> (raw)
In-Reply-To: <001d01c2bdd9$191b07f0$6700a8c0@mattspc>
References: <001d01c2bdd9$191b07f0$6700a8c0@mattspc>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, cobalt@bearfruit.org
Subject: Re: cannot create function that uses variable table name
In-Reply-To: <200301170448.29838.darcy@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox