Обсуждение: function, that uses different table(names)
Hello group,<br /><br /> I've a bunch of tables, which have just about the same name. They are just iterated like this:<br/><br /> tbl_table1<br /> tbl_table2<br /> tbl_table3<br /> ...<br /><br /> They all have the same field, but differentdata.<br /><br /> Now I'm wondering, if I have to write functions for every table, although they perform the sameoperation?<br /> Or is it possible to pass an integer parameter to the function, use it to put the right tablename togetherand perform the operation.<br /><br /> This would something look like this:<br /><br /> CREATE OR REPLACE functiongetmaxuserid(integer) RETURNS integer AS'<br /> DECLARE tmp_tmp_tmp_table varchar(100);<br /> DECLARE tmp_tablevarchar(100);<br /> DECLARE tmp_tmp_table varchar(100);<br /> DECLARE live_table varchar(100);<br/> DECLARE i_return integer;<br /> BEGIN<br /> --live_table := ''tbl_highscore_app'' || cast($1as varchar);<br /> SELECT max(userid) FROM ''tbl_highscore_app'' || cast($1 as varchar);<br /> <br /> <br /><br /> return(0);<br /> END;<br /> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;<br/><br /><br /> I don't know if this is good practise, but I'm trying to create a function which gets an integer.<br clear="all" /><br /><br />
Sorry, pushed the send - button by accident. So here is the right text:
CREATE OR REPLACE function getmaxuserid(integer) RETURNS integer AS'
DECLARE live_table varchar(100);
DECLARE i_return integer;
BEGIN
live_table := ''tbl_highscore_app'' || cast($1 as varchar);
SELECT i_return = max(userid) FROM ''tbl_highscore_app'' || cast($1 as varchar);
return(i_return);
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
My parser tells me that this is not possible.
Does anybody has an advice, how to fix my problem? I don't want to write this function 28 times for 28 different tables...
Thanx in advance,
Moritz
CREATE OR REPLACE function getmaxuserid(integer) RETURNS integer AS'
DECLARE live_table varchar(100);
DECLARE i_return integer;
BEGIN
live_table := ''tbl_highscore_app'' || cast($1 as varchar);
SELECT i_return = max(userid) FROM ''tbl_highscore_app'' || cast($1 as varchar);
return(i_return);
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
My parser tells me that this is not possible.
Does anybody has an advice, how to fix my problem? I don't want to write this function 28 times for 28 different tables...
Thanx in advance,
Moritz
Hello group,
I've a bunch of tables, which have just about the same name. They are just iterated like this:
tbl_table1
tbl_table2
tbl_table3
...
They all have the same field, but different data.
Now I'm wondering, if I have to write functions for every table, although they perform the same operation?
Or is it possible to pass an integer parameter to the function, use it to put the right tablename together and perform the operation.
This would something look like this:
CREATE OR REPLACE function getmaxuserid(integer) RETURNS integer AS'
DECLARE tmp_tmp_tmp_table varchar(100);
DECLARE tmp_table varchar(100);
DECLARE tmp_tmp_table varchar(100);
DECLARE live_table varchar(100);
DECLARE i_return integer;
BEGIN
--live_table := ''tbl_highscore_app'' || cast($1 as varchar);
SELECT max(userid) FROM ''tbl_highscore_app'' || cast($1 as varchar);
return(0);
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
I don't know if this is good practise, but I'm trying to create a function which gets an integer.
am 02.11.2005, um 14:30:38 +0100 mailte Moritz Bayer folgendes: > Hello group, > > I've a bunch of tables, which have just about the same name. They are just > iterated like this: > > tbl_table1 > tbl_table2 > tbl_table3 > ... > > They all have the same field, but different data. > > Now I'm wondering, if I have to write functions for every table, although > they perform the same operation? > Or is it possible to pass an integer parameter to the function, use it to > put the right tablename together and perform the operation. Yes, this is possible. Please read the documentation: http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
am 02.11.2005, um 14:36:06 +0100 mailte Moritz Bayer folgendes: > live_table := ''tbl_highscore_app'' || cast($1 as varchar); > SELECT i_return = max(userid) FROM ''tbl_highscore_app'' || cast($1 as > varchar); > > My parser tells me that this is not possible. Correct. You should build your string with the query and execute this string. http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
Thanks, <br /> great thing! Since I have an select statement, I'll have to use a cursor. Just as execute I haven't used itbefore. So I can say that I've learned something today :) <br /><br /> Thaks again,<br /> Moritz<br />
Moritz Bayer <moritz.bayer@googlemail.com> writes:
> I've a bunch of tables, which have just about the same name. They are just
> iterated like this:
> tbl_table1
> tbl_table2
> tbl_table3
> ...
> They all have the same field, but different data.
> Now I'm wondering, if I have to write functions for every table, although
> they perform the same operation?
> Or is it possible to pass an integer parameter to the function, use it to
> put the right tablename together and perform the operation.
As noted elsewhere, you could do that by building query strings and
EXECUTE'ing them ... but you really ought to rethink your table layout,
instead. The SQL-ish way to do this is to combine the tables into one
big table with an extra key column that stores whatever condition
distinguished the smaller tables in your mind.
regards, tom lane