From: Matthew Nuzum <cobalt@bearfruit.org>
To: pgsql-sql@postgresql.org
Subject: cannot create function that uses variable table name
Date: Thu, 16 Jan 2003 22:32:37 -0500
Message-ID: <001d01c2bdd9$191b07f0$6700a8c0@mattspc> (raw)
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
cobalt@bearfruit.org
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: cobalt@bearfruit.org
Subject: Re: cannot create function that uses variable table name
In-Reply-To: <001d01c2bdd9$191b07f0$6700a8c0@mattspc>
* 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