Обсуждение: cannot create function that uses variable table name
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_nameALIAS 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 | Goodbye | 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
--- Matthew Nuzum <cobalt@bearfruit.org> wrote: > 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: build the query as a string and execute it. __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
On Thu, 16 Jan 2003, 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: > > 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'; You'll need to look into EXECUTE. You also are going to have to watch out for concurrency issues since two transactions calling this function at the same time for the same args are likely to give incorrect results.
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 doesnt work. It
> always gives me a parse error at $1. Heres 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.
As per Matthew Nuzum's post My query is very similar... I need sql functions to update the database. If I specify the filename etc they work. BUT that leads to 6 functions which are exactly the same apart from the file they update. 1) why can't I use a variable name and 2) could someone please point me towards some examples of EXECUTE if thats the only way to do it? my example is: CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS' BEGIN UPDATE $1 SET "Retired" = 'true' WHERE $2 = $3; SELECT (whatever to return the int4); END;' Language 'plpgsql'; which has: parse error at or near "true" ($1 is the filename, $2 is the fieldname, $3 is the fieldvalue and "Retired" is a boolean field in each of the files) Thanks Beth
Thanks for your reply David...
1)The "" round Retired are to label the column/field 'Retired' rather
than 'retired' (someone else created the database with Upper case
titles!)
2) Your code is correct.. cept that single quotes have to be escaped(?!)
so the following will do the trick when updating text fields...
CREATE FUNCTION temp(text,text,int4) RETURNS integer AS '
DECLARE
update_table ALIAS FOR $1;
update_field ALIAS FOR $2;
update_id ALIAS FOR $3;
BEGIN
EXECUTE ''UPDATE ''|| quote_ident(update_table) || '' SET "Retired" =
''''true'''' WHERE '' || quote_ident(update_field) || '' = '' ||
quote_literal(update_id);
RETURN update_id;
END;
' language 'plpgsql';
which creates...
and: select temp('TableName', 'TableID', 20);
returns 20.
On Fri, 2003-01-24 at 13:13, David Durst wrote:
> > I need sql functions to update the database. If I specify the filename
> > etc they work. BUT that leads to 6 functions which are exactly the same
> > apart from the file they update.
> >
> > 1) why can't I use a variable name and
> > 2) could someone please point me towards some examples of EXECUTE if
> > thats the only way to do it?
> >
> >
> > my example is:
> >
>
> This should work
>
> CREATE FUNCTION retire(varchar, varchar, int4) returns int4 AS'
> DECLARE
> varone ALIAS FOR $1;
> vartwo ALIAS FOR $2;
> varthr ALIAS FOR $3;
> BEGIN
> UPDATE varone SET "Retired" = 'true' WHERE vartwo = varthr;
> SELECT (whatever to return the int4);
> END;'
> Language 'plpgsql';
>
>
> And I am not sure of the purpose for the "" around Retired