X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4EA95D836A for ; Wed, 2 Nov 2005 09:36:05 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44427-10 for ; Wed, 2 Nov 2005 13:36:02 +0000 (GMT) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.199]) by svr1.postgresql.org (Postfix) with ESMTP id AE0A4D8393 for ; Wed, 2 Nov 2005 09:36:03 -0400 (AST) Received: by xproxy.gmail.com with SMTP id t11so199730wxc for ; Wed, 02 Nov 2005 05:36:06 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=googlemail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=JHn0QiVCmnaeffno8fji23B1Ez2Eo1xUCNukToO9yK1u2HUn7OSSFXJjj75A/wHOMGAaGxvUPerTQpl6sKx5wa+C15NcRFrlmzP6uBvxdOl7nMf6Jqy/EJeq5SjZcJIlyTbh2bU8i8BYDtDZaf+Trdimh7Q+6LTJWXkwAyr49YY= Received: by 10.64.241.1 with SMTP id o1mr1583432qbh; Wed, 02 Nov 2005 05:36:06 -0800 (PST) Received: by 10.64.232.1 with HTTP; Wed, 2 Nov 2005 05:36:06 -0800 (PST) Message-ID: Date: Wed, 2 Nov 2005 14:36:06 +0100 From: Moritz Bayer To: pgsql-sql@postgresql.org Subject: Re: function, that uses different table(names) In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2761_10167355.1130938566523" References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.667 required=5 tests=[AWL=0.666, HTML_MESSAGE=0.001] X-Spam-Score: 0.667 X-Spam-Level: X-Archive-Number: 200511/22 X-Sequence-Number: 23274 ------=_Part_2761_10167355.1130938566523 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 :=3D ''tbl_highscore_app'' || cast($1 as varchar); SELECT i_return =3D 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 jus= t > 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 :=3D ''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 functio= n > which gets an integer. > > > ------=_Part_2761_10167355.1130938566523 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Sorry, pushed the send - button  by accident. So here is the right tex= t:

CREATE OR REPLACE function getmaxuserid(integer) RETURNS integer AS'
     DECLARE live_table varchar(100);
      DECLARE i_return integer;
BEGIN
     live_table :=3D ''tbl_highscore_app'' || cast($1 a= s varchar);
     SELECT   i_return =3D 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 th= is 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,  alth= ough 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 :=3D ''tbl_highscore_app'' || cast($1= as varchar);
     SELECT   max(userid) FROM ''tbl_highscor= e_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.








------=_Part_2761_10167355.1130938566523--