From: Chris Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: stored procs in postgresql
Date: Sat, 24 Sep 2005 17:27:19 -0400
Message-ID: <60vf0qma14.fsf@dba2.int.libertyrms.com> (raw)
References: <op.sxmghwnc1qrx7c@localhost>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: stored procs in postgresql
In-Reply-To: <60vf0qma14.fsf@dba2.int.libertyrms.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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