Re: stored procs in postgresql

ceremona(at)gmail(dot)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