From: Chris Browne <cbbrowne@acm.org>
Subject: Re: stored procs in postgresql
Date: Sat, 24 Sep 2005 17:27:19 -0400
Organization: cbbrowne Computing Inc
Lines: 37
Message-ID: <60vf0qma14.fsf@dba2.int.libertyrms.com>
References: <op.sxmghwnc1qrx7c@localhost>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.17 (Jumbo Shrimp, linux)
Cancel-Lock: sha1:5FvzPRxgiY+DAfeGBCNsIfJSMrs=
To: pgsql-sql@postgresql.org

ceremona@gmail.com writes:
> I have been having some trouble with plsql stored procs in postgres in
> that I can
> make a table name a variable in the stored proc.  Is there some
> special  way to make this happen that I am unaware of?
>
> For example, I want to do something like:
>
> stored_proc(integer,varchar)
>
> SELECT table_name.id
>     FROM table_name $2
>    WHERE table_name.id=$1
>
> but I get an error about the $2 argument being no good.
>
> Does anyone know how I can deal with this?

To do this sort of thing, you need to build up the query as a string,
and EXECUTE it.

Thus...
   query := 'select t.id from ' || $2 || ' t where t.id = ' || $1 || ';';

The other vital problem is that the select is in bad form.  The actual
name of the table needs to come BEFORE the alias, not after.

The following would represent more nearly legitimate SQL... 

  SELECT table_name.id
      FROM $2 table_name 
     WHERE table_name.id=$1
-- 
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/spiritual.html
And me, with this terrible pain in all the diodes down my left side...
-- Marvin the Paranoid Android