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:
CREATE OR REPLACE FUNCTION get_last_dsply_order(
varchar, -- tablename
varchar, -- id_col_name
varchar) -- where_item
RETURNS integer AS '
DECLARE total_items integer;
tablename ALIAS FOR $1;
id_col_name ALIAS FOR $2;
where_item ALIAS FOR $3;
BEGIN
SELECT INTO total_items count(*) FROM tablename WHERE id_col_name
= where_item;
RETURN total_items;
END;
' LANGUAGE 'plpgsql';
Here’s some sample data so that you can better see what I’m doing:
Fileid| accountid | filename | dsply_order
==============================================
1| account1 | My File | 1
2| account1 | Another file | 2
3| account1 | YA File | 3
4| account2 | Hello world | 1
5| account2 | Hi again | 2
6| account3 | Good bye | 3
7| account4 | Mom | 2
8| account4 | Dad | 1
=============================================
Therefore you would want to see the last item number used by account2 so
that you can add a new item to the end of the list. You might do
something like this:
INSERT INTO files (accountid, filename, dsply_order) VALUES
(‘account2’,’Testing’,get_last_dsply_order(‘files’,’accountid’,’account2
’));
BTW, it will have a complementary trigger assigned to each table that
upon delete will shift all the items up 1 to fill in the gap left by the
deleted item. Therefore the count() of the items in the table should
also match the highest numbered item.
--
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
