pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction, that uses different table(names)
6+ messages / 3 participants
[nested] [flat]
* function, that uses different table(names)
@ 2005-11-02 13:30 Moritz Bayer <moritz.bayer@googlemail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Moritz Bayer @ 2005-11-02 13:30 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function, that uses different table(names)
@ 2005-11-02 13:36 Moritz Bayer <moritz.bayer@googlemail.com>
parent: Moritz Bayer <moritz.bayer@googlemail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Moritz Bayer @ 2005-11-02 13:36 UTC (permalink / raw)
To: pgsql-sql
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
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.
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function, that uses different table(names)
@ 2005-11-02 13:38 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Moritz Bayer <moritz.bayer@googlemail.com>
2 siblings, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2005-11-02 13:38 UTC (permalink / raw)
To: pgsql-sql
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-...
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function, that uses different table(names)
@ 2005-11-02 13:56 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Moritz Bayer <moritz.bayer@googlemail.com>
0 siblings, 1 reply; 6+ messages in thread
From: A. Kretschmer @ 2005-11-02 13:56 UTC (permalink / raw)
To: pgsql-sql
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-...
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function, that uses different table(names)
@ 2005-11-02 14:23 Moritz Bayer <moritz.bayer@googlemail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 0 replies; 6+ messages in thread
From: Moritz Bayer @ 2005-11-02 14:23 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
Thanks,
great thing! Since I have an select statement, I'll have to use a cursor.
Just as execute I haven't used it before. So I can say that I've learned
something today :)
Thaks again,
Moritz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function, that uses different table(names)
@ 2005-11-02 14:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Moritz Bayer <moritz.bayer@googlemail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-11-02 14:54 UTC (permalink / raw)
To: Moritz Bayer <moritz.bayer@googlemail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-11-02 14:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-02 13:30 function, that uses different table(names) Moritz Bayer <moritz.bayer@googlemail.com>
2005-11-02 13:36 ` Moritz Bayer <moritz.bayer@googlemail.com>
2005-11-02 13:56 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-11-02 14:23 ` Moritz Bayer <moritz.bayer@googlemail.com>
2005-11-02 13:38 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2005-11-02 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
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