Received: from www-uk-05.digiserve.com ([195.172.23.63]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2574Px41762 for ; Mon, 5 Mar 2001 02:04:25 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id HAA09908; Mon, 5 Mar 2001 07:05:29 GMT Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id 2B8E02A476; Sun, 4 Mar 2001 19:27:11 +0000 (GMT) Message-ID: <3AA29705.B1D291D6@archonet.com> Date: Sun, 04 Mar 2001 19:27:00 +0000 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Gerald Gutierrez Cc: pgsql-sql@postgresql.org Subject: Re: Passing table names to PL/PGSQL for SELECT/UPDATE/INSERT References: <5.0.2.1.0.20010303202117.02a09d50@mail.rchmd1.bc.wave.home.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/47 X-Sequence-Number: 1692 Gerald Gutierrez wrote: > > I'd like to generalize my function. As per Richard Huxton's suggestion to > create tables named after session ID (thanks Richard)s, I'd like to pass in > some table names so that the algorithm can read from and write into tables > that I specify as parameters to the function. Sometihng like: Known limitation - sorry, should have made things clear. > select dijkstra(inputtablename, outputtablename); > > I've tried typing the parameters as TEXT, and then just inserting $1 in the > select (e.g. SELECT * from $1 ...) This doesn't work, nor did a number of > other ways I tried. Basically, I can think of 3 solutions. Bear in mind it's late here, I've had a few drinks with dinner. 1. Use the EXECUTE statement in 7.1 to run the query 2. Use the EXECUTE statement to construct a custom function, one for each session with the table hard-coded (you could also do this from the application I'd guess) 3. Try pl/Tcl - I _think_ that lets you construct a dynamic query, but I don't know TCL so can't say (it's supposed to be easy enough but I've never got round to it). HTH - Richard Huxton