Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C574E476C13 for ; Thu, 16 Jan 2003 22:35:49 -0500 (EST) Received: from smtp-server4.tampabay.rr.com (smtp-server4.tampabay.rr.com [65.32.1.43]) by postgresql.org (Postfix) with ESMTP id 9A0844763A9 for ; Thu, 16 Jan 2003 22:32:41 -0500 (EST) Received: from mattspc ([65.34.52.169]) by smtp-server4.tampabay.rr.com (8.12.2/8.12.2) with ESMTP id h0H3WkeK025619 for ; Thu, 16 Jan 2003 22:32:46 -0500 (EST) From: "Matthew Nuzum" To: Subject: cannot create function that uses variable table name Date: Thu, 16 Jan 2003 22:32:37 -0500 Organization: Bearfruit.org Message-ID: <001d01c2bdd9$191b07f0$6700a8c0@mattspc> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.3416 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/271 X-Sequence-Number: 11136 I have a number of tables in my database that use the concept of =93display order=94, which is a field that can be used in an order by clause to dictate what order the results should come out in. =A0 I thought I would be crafty and devise a function that would always return the highest numbered item in the table. But it doesn=92t work. It always gives me a parse error at $1. Here=92s 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 =3D where_item; RETURN total_items; END; ' LANGUAGE 'plpgsql'; Here=92s some sample data so that you can better see what I=92m doing: Fileid| accountid | filename | dsply_order =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D 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 (=91account2=92,=92Testing=92,get_last_dsply_order(=91files=92,=92accountid= =92,=92account2 =92));=20 =A0 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@bearfruit.org