From: | "Matthew Nuzum" <cobalt(at)bearfruit(dot)org> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | cannot create function that uses variable table name |
Date: | 2003-01-17 03:32:37 |
Message-ID: | 001d01c2bdd9$191b07f0$6700a8c0@mattspc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 doesnt work. It
always gives me a parse error at $1. Heres 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';
Heres some sample data so that you can better see what Im 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
cobalt(at)bearfruit(dot)org
From | Date | Subject | |
---|---|---|---|
Next Message | chester c young | 2003-01-17 04:42:07 | Re: cannot create function that uses variable table name |
Previous Message | Josh Berkus | 2003-01-16 23:42:14 | Re: query speed joining tables |