Postgres Pro
Downloads
Home   >   mailing lists

Thread: stored procs in postgresql

stored procs in postgresql

From
ceremona@gmail.com
Date:
Hi,

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?

Thanks,
Cere

Re: stored procs in postgresql

From
Chris Browne
Date:
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

Re: stored procs in postgresql

From
Cere Davis
Date:
thanks,

I didn't even know about the string concatination function for this.
Unfortunately, it was of no help.  Specificly I am trying this, with
the following error:

SQL error:


ERROR:  syntax error at or near "' || $2 || '" at character 110

In statement:


CREATE FUNCTION "getcensusbound" (geometry, character) RETURNS
character AS 'SELECT bound_table.name  FROM '' || $2 || '' bound_table WHERE bound_table.the_geom::bytea !=
''null''::byteaAND 
contains(geometryn(bound_table.the_geom, 1), $1) = true;' LANGUAGE
"sql"
RETURNS NULL ON NULL INPUT


-Cere


On 9/24/05, Chris Browne <cbbrowne@acm.org> wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>


--
Cere Davis
ceremona@gmail.com
-------------------
GPG Key:  http://staff.washington.edu/cere/pubkey.asc
GPG fingerprint (ID# 73FCA9E6) : F5C7 627B ECBE C735 117B  2278 9A95
4C88 73FC A9E6

Re: stored procs in postgresql

From
"A. Kretschmer"
Date:
am  24.09.2005, um 21:10:48 -0700 mailte Cere Davis folgendes:
> thanks,
> 
> I didn't even know about the string concatination function for this. 
> Unfortunately, it was of no help.  Specificly I am trying this, with
> the following error:
> 
> SQL error:
> 
> 
> ERROR:  syntax error at or near "' || $2 || '" at character 110
> 
> In statement:
> 
> 
> CREATE FUNCTION "getcensusbound" (geometry, character) RETURNS
> character AS 'SELECT bound_table.name

1. you should better use dollar-quoting
Read:
http://www.postgresql.org/docs/8.0/static/plpgsql-development-tips.html


>    FROM '' || $2 || '' bound_table
>   WHERE bound_table.the_geom::bytea != ''null''::bytea AND
> contains(geometryn(bound_table.the_geom, 1), $1) = true;' LANGUAGE
> "sql"

2. you must build a string with your query and execute the string.
Read:
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


> On 9/24/05, Chris Browne <cbbrowne@acm.org> wrote:
> > ceremona@gmail.com writes:

3. please learn to quote.
Read: http://learn.to/quote
http://www.netmeister.org/news/learn2quote.html


Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net===    Schollglas Unternehmensgruppe    ===