Thread: cannot create function that uses variable table name

cannot create function that uses variable table name

From

"Matthew Nuzum"

Date:

17 January 2003, 01:35:50

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

Re: cannot create function that uses variable table name

From

chester c young

Date:

17 January 2003, 02:42:05

--- 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

Re: cannot create function that uses variable table name

From

Stephan Szabo

Date:

17 January 2003, 02:44:48

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.

Re: cannot create function that uses variable table name

From

"D'Arcy J.M. Cain"

Date:

17 January 2003, 07:48:33

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.

Re: cannot create function that uses variable table name

From

Beth

Date:

23 January 2003, 20:01:54

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

Re: cannot create function that uses variable table name

From

Beth

Date:

24 January 2003, 00:36:19

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